gpt4 book ai didi

MySQL如何检查值是否以字母开头并以数字结尾?

转载 作者:行者123 更新时间:2023-11-29 15:50:02 24 4
gpt4 key购买 nike

我想显示特定数据,其中所有值仅在堪萨斯市以字母开头并以数字结尾。

我的 table 是什么样的:

酒店房间

pID     |name       |  city |   key
--------------------------------------------
543 |H. Stein |Cansas | 16Z004
542 |Z. Francis |Cansas | Z10-30
642 |Q. Snake |Cansas | Z10-25
645 |P. Brown |Kentucky | Z10-40

我想要什么:

pID     |name       |  city |   key
--------------------------------------------
542 |Z. Francis |Cansas | Z10-30
642 |Q. Snake |Cansas | Z10-25

我尝试过但没有成功:

SELECT * FROM hotelroom
WHERE city LIKE '%Cansas%' AND
key LIKE '^[abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ].*[0123456789]$'

最佳答案

您应该使用REGEXP , WHERE 字段 REGEXP regex_string。另外,您不必写出所有字母,您可以像 [a-zA-Z] 那样写。

To be specific: a range is a contiguous series of characters, from low to high, in the ASCII character set.[101] For example, [z-a] is not a range because it's backwards. The range [A-z] matches both uppercase and lowercase letters, but it also matches the six characters that fall between uppercase and lowercase letters in the ASCII chart: [, \, ], ^, _, and '.

https://docstore.mik.ua/orelly/unix3/upt/ch32_08.htm

SELECT * FROM hotelroom
WHERE city LIKE '%Cansas%' AND
key REGEXP '^[a-zA-Z].*[0-9]$'

另外,

I want to show specific data where all the values start with a letter and ends with a number in the city of cansas only.

如果您只想获取堪萨斯市的结果,则不必使用LIKE。如果您使用 LIKE,它还会匹配 Cansas2 城市或以 Cansas 作为子字符串的任何内容。

您可以使用 equals (=) operator .

SELECT * FROM hotelroom
WHERE city = 'Cansas' AND
key REGEXP '^[a-zA-Z].*[0-9]$'

关于MySQL如何检查值是否以字母开头并以数字结尾?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/56813576/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com