1

I am trying to write an mysql script to see if the first character is a number or not, this is what I go so far

select CASE WHEN user_id REGEXP '[0-9]+' then 1 else 0 end as user_id  from table

it returns all my data as 1...the colum is a varchar, I will have user_id like this 1234 or this USER-484 or `ADMIN-464567' IS what I am trying to do possible?

Cœur
  • 37,241
  • 25
  • 195
  • 267
user1269625
  • 3,121
  • 26
  • 79
  • 111
  • I'm not entirely sure what you try to do. Can you please clarify? – muehlbau Jan 29 '13 at 18:36
  • FYI - `CASE` returns the result of the first true condition - e.g. `CASE WHEN [logical condition] THEN X ELSE Y END`, returns the X or Y but nothing from the [logical condition] area – Charleh Jan 29 '13 at 18:54
  • dup of http://stackoverflow.com/questions/14447048, http://stackoverflow.com/questions/4511945, http://stackoverflow.com/questions/1214035 – ysth Jan 29 '13 at 18:54

3 Answers3

2

Try this to check if starting letter/digit is a number:

select CASE WHEN user_id
REGEXP '^[0-9]+' 
then 1 else 0 end as user_id
from table;

or

select CASE WHEN user_id
REGEXP '^\d+' 
then 1 else 0 end as user_id
from table;

I am just wondering about your query arrangement. I thought it should be like this,

select user_id
from table
where REGEXP '^\d+' ;

But yours is actually working with the correct regex. :)

* SQLFIDDEL DEMO

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • @user1269625 +1 for the question, coz I haven't used earlier the way you have used the `regex function`. And it seems to work :) with the correct `regex` given. – bonCodigo Jan 29 '13 at 18:47
1

So you want to display the user_id instead? Then put your regexp in the WHERE clause

select user_id from table
where
user_id REGEXP '^[0-9]+'

Also you're missing a ^ which means "at the beginning of the line"

fancyPants
  • 50,732
  • 33
  • 89
  • 96
0

Since you are missing ^ before [0-9] it matches numbers at any place in your field. By specifying ^[0-9] it means that it should BEGIN with a number. So try using '^[0-9]'

sasikt
  • 5,324
  • 1
  • 17
  • 16