CODEWITHSUNDEEP
mysqlsqldatabase
Mashiba Sakuto
Mashiba Sakuto

Reputation: 223

MySQL how to check if value starts with letter and ends with a number?

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.

What my table looks like:

hotelroom

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

What I want:

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

What I tried and did not work:

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

Upvotes: 2

Views: 5228

Answers (2)

Andra
Andra

Reputation: 1382

You should use REGEXP, WHERE fields REGEXP regex_string. Also, you don't have to write all of the alphabet letter, you could just write it like [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]$'

Also,

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.

If you only want to get the result from the city of Cansas, you don't have to use LIKE. If you're using LIKE, it will also match Cansas2 city or anything that has Cansas as it's substring.

You could just use equals (=) operator.

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

Upvotes: 2

Jens
Jens

Reputation: 69440

You should use RLIKE instead of LIKE

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

The documentation you can find here: https://dev.mysql.com/doc/refman/8.0/en/regexp.html#operator_regexp

Upvotes: 1

Related Questions