Geek asks and answers

MySQL LIKE Point Separator Query

I have a field in mysql type varchar where these fields contain code and the code looks like a tree structure with a dot (.) As a separator between parent and child.

An example 1215has a child 1215.001and1215.002

And this is the data of each row in the 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 a level 2 code?
which will only mean code 1215.001and1215.002

Already tried with this request

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

But it runs all the code using 1215for example:1215.001.001

+3
source share
3 answers

Use regex.

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

This will fit all:

 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.("$")
+4
source

You can use a regular expression for this;

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

, ^1215, ., [0-9]+, $.

1215.001 1215.002, 1215 1215.001.001

, , Dave

+2
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

0
source

More articles:


All Articles