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

Question

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

我的 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]$'

Answer 1

您应该使用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]$'