select - MySQL - If It Starts With A Number Or Special Character

Question

SELECT * 
FROM `thread` 
WHERE forumid NOT IN (1,2,3) AND IF( LEFT( title, 1) = '#', 1, 0)
ORDER BY title ASC

I have this query which will select something if it starts with a #. What I want to do is if # is given as a value it will look for numbers and special characters. Or anything that is not a normal letter.

How would I do this?

Answer 1

If you want to select all the rows whose "title" does not begin with a letter, use REGEXP:

  SELECT * 
    FROM thread 
   WHERE forumid NOT IN (1,2,3)
     AND title NOT REGEXP '^[[:alpha:]]'
ORDER BY title ASC

• NOT means "not" (obviously ;))
• ^ means "starts with"
• [[:alpha:]] means "alphabetic characters only"

Find more about REGEXP in MySQL's manual.