CODEWITHSUNDEEP
mysqlregexsql-like
Miky
Miky

Reputation: 129

Select all where [first letter starts with number or special characters]

I want to search in database the title start by a letter, number or special characters.

For letter is good I use this:

SELECT author FROM lyrics WHERE author LIKE 'B%';

But for number and special characters I need your help

SELECT author FROM lyrics WHERE author LIKE '[0-9]%';

Upvotes: 3

Views: 3259

Answers (2)

chris85
chris85

Reputation: 23880

The LIKE operator will not function like you want here, it takes static values and wildcards (% or _). You should use a regular expression with the mysql REGEXP. Something like: 

SELECT author FROM lyrics WHERE author regexp '^[0-9B]'

Should find all authors that start with a B or a number. The ^ is a leading anchor meaning the string must start there. The [] builds a character class which is a list of characters, or a range when the - is used, 0-9 is any single number.

It is unclear what your "special characters" are but you might consider a negated character class, that is a list of not allowed characters. E.g.

SELECT author FROM lyrics WHERE author regexp '^[^.!?]'

would list all authors that didn't start with a ., !, or ?.

Regex demos: https://regex101.com/r/qjjmNq/1/, https://regex101.com/r/qjjmNq/2/

Upvotes: 4

Matt
Matt

Reputation: 15061

Use REGEXP rather than LIKE

SELECT author 
FROM lyrics 
WHERE author REGEXP '[^[:alnum:]]'

Upvotes: 2

Related Questions