CODEWITHSUNDEEP
mysql
GusDeCooL
GusDeCooL

Reputation: 5761

Query MySQL LIKE with dot separator

i have field in mysql type varchar with field data contains code, and the code is like tree structure with dot (.) as separator between parent and child.

Example 1215 has child 1215.001 and 1215.002

And this is the data each row on database

ID  | kode
1   | 1215
2   | 1215.001
3   | 1215.001.001
4   | 1215.002.001
5   | 1215.002
6   | 1215.002.001

How to get the level 2 code?
which its mean will be only the code 1215.001 and 1215.002

Already tried with this query 

select * from `kegiatan` where `kode` LIKE '1215.%';

But it get all the code start with 1215 eg: 1215.001.001

Upvotes: 3

Views: 4818

Answers (3)

Dave Rix
Dave Rix

Reputation: 1689

You could use a regular expression for this;

select * from `kegiatan` where `kode` REGEXP '^1215.[0-9]+$';

Which will match items beginning with ^1215, followed by a period ., followed by one or more numeric values [0-9]+, followed by the end of the string $.

This will match 1215.001 and 1215.002, but not 1215 or 1215.001.001

Hope it helps, Dave

Upvotes: 2

scragar
scragar

Reputation: 6824

Use a regular expression.

 select * from `kegiatan` where `kode` REGEXP '^1215\.[^\.]+$';

This will match everything:

 That starts with ( "^" )

 the string "1215.",

 followed by at least one character that is a member of the set "NOT ." ("[^\.]+")

  followed by the end of the string.("$")

Upvotes: 5

Denys Séguret
Denys Séguret

Reputation: 382504

select distinct substr(kode,1,8)  from `kegiatan` where `kode` LIKE '1215.%';

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_substr

Upvotes: 0

Related Questions