CODEWITHSUNDEEP
mysqlsql
DigitalMusicology
DigitalMusicology

Reputation: 251

Pattern for LIKE in SQL Statement

I would like to select all rows that start with any character.

SELECT * FROM table WHERE field LIKE '[a-z]%' ;

The type of rows I would like to find look like this:

ID  DATA

993 DEF055900960

994 DEF055900961

995 DEF055900964

996 DEF056102254

997 DEF056131201

I have unsucessfully tried RLIKE and REGEXP and also added upper case A-Z ot the pattern.

Why is the following not working?

Upvotes: 0

Views: 151

Answers (4)

ʰᵈˑ
ʰᵈˑ

Reputation: 11375

Use REGEXP

http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp

For example, assume we have this data set.

mysql> select * from a;
+------+
| b    |
+------+
| abc  |
| zxxb |
| kkfy |
| 0002 |
+------+
4 rows in set

We want to select everything with the a-z pattern

mysql> SELECT * FROM a WHERE b REGEXP BINARY '[a-z]';
+------+
| b    |
+------+
| abc  |
| zxxb |
| kkfy |
+------+
3 rows in set

SQLFiddle

For your data set

SQLFiddle

Use the regular expression [a-zA-Z0-9]

mysql> SELECT * FROM a WHERE b REGEXP BINARY '[a-zA-Z0-9]';
+--------------+
| b            |
+--------------+
| DEF055900960 |
| DEF055900961 |
| DEF055900964 |
| DEF056102254 |
| DEF056131201 |
+--------------+
5 rows in set

Upvotes: 0

Nisarg
Nisarg

Reputation: 3282

Try this. 

SELECT `firstname`
FROM `users`
WHERE firstname
REGEXP BINARY '^[A-Z]'

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

SELECT * FROM table WHERE field RLIKE '[a-z]' ;

SQL Fiddle Demo

I went through here to read about Pattern Matching in Mysql

Upvotes: 2

Thanos Markou
Thanos Markou

Reputation: 2624

Try this instead:

SELECT * FROM table WHERE field LIKE '[^A-Z]%';

Upvotes: 0

Related Questions