I'm trying to select all rows that contain only alphanumeric characters in MySQL using:
SELECT * FROM table WHERE column REGEXP '[A-Za-z0-9]'; However, it's returning all rows, regardless of the fact that they contain non-alphanumeric characters.
Add a comment |
6 Answers
Try this code:
SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9]+$' This makes sure that all characters match.
4 Comments
Md. Mahabubur Rahman
Okey it done properly, but it does not show space including data field. How do i get space including data field by this query.
Aaron Digulla
@Md.MahabuburRahman: All a blank between
9 and ]: '^[A-Za-z0-9 ]+$'
chungtinhlakho
is regexp efficient? I remember doing this with isnumeric on the last character. this is interesting. thanks select name from birt where isnumeric(RIGHT(name,1)) =1
Aaron Digulla
@chungtinhlakho No, regexp is very inefficient compared to
isnumeric() Use specialized functions when you can.Your statement matches any string that contains a letter or digit anywhere, even if it contains other non-alphanumeric characters. Try this:
SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9]+$'; ^ and $ require the entire string to match rather than just any portion of it, and + looks for 1 or more alphanumberic characters.
You could also use a named character class if you prefer:
SELECT * FROM table WHERE column REGEXP '^[[:alnum:]]+$'; 
Comments
Try this:
REGEXP '^[a-z0-9]+$' As regexp is not case sensitive except for binary fields.
Comments
There is also this:
select m from table where not regexp_like(m, '^[0-9]\d+$') which selects the rows that contains characters from the column you want (which is m in the example but you can change).
Most of the combinations don't work properly in Oracle platforms but this does. Sharing for future reference.
Comments
Try this
select count(*) from table where cast(col as double) is null; 
Comments
Change the REGEXP to Like
SELECT * FROM table_name WHERE column_name like '%[^a-zA-Z0-9]%' this one works fine
