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.

Answer 2

it's POSSIBLE you can try to cast it as a char:

CAST('#' AS CHAR)

but i don't know if this will work for the octothorpe (aka pound symbol :) ) because that's the symbol for starting a comment in MySQL

Answer 3

  SELECT t.* 
    FROM `thread` t
   WHERE t.forumid NOT IN (1,2,3) 
     AND INSTR(t.title, '#') = 0
ORDER BY t.title

Use the INSTR to get the position of a given string - if you want when a string starts, check for 0 (possibly 1 - the documentation doesn't state if it's zero or one based).