CODEWITHSUNDEEP
mysqlselect
Ben Shelock
Ben Shelock

Reputation: 20965

MySQL - If It Starts With A Number Or Special Character

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?

Upvotes: 7

Views: 11508

Answers (3)

Josh Davis
Josh Davis

Reputation: 28730

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.

Upvotes: 21

OMG Ponies
OMG Ponies

Reputation: 332541

  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).

Upvotes: 1

Jason
Jason

Reputation: 52523

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

Upvotes: 1

Related Questions