CODEWITHSUNDEEP
mysql
Franco
Franco

Reputation: 2926

Finding only strings starting with a number using MySQL LIKE

How can I find strings in a table where the first character is a number?

I'm using MySQL LIKE as follows

SELECT   DISTINCT label_no_country
FROM     releases
WHERE    label_no_country LIKE '$letter%'  
ORDER BY label_no_country

where $letter is a letter between A-Z (depending on the input)

So if $letter == 'A' then it will show all entries where the first letter is A.

How can I run this query so that it will show records that start with numbers?

e.g.

1st record

cheers!

Upvotes: 4

Views: 13262

Answers (2)

egrunin
egrunin

Reputation: 25053

You might want to use Regular Expressions:

SELECT DISTINCT label_no_country FROM releases 
WHERE label_no_country 
REGEXP '^[0-9]'

See MySQL docs for details.

Upvotes: 27

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

WHERE label_no_country >= '0' AND label_no_country <= '9zzzzzzzzzzzzz' -- Insert as many z:s as the field can hold

EDIT: Or better yet, Just put in the character that is just after 9 in your collating sequence:

WHERE label_no_country >= '0' AND label_no_country < ':' -- assuming : is the character after 9 in your collating sequence

Upvotes: 1

Related Questions