Search code examples
mysqlregexsql-like

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]%';
Solution

  • 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/