I am getting weird answers with regularexpression with setlist on mysql

Question

I have 3 students with the surname 'kim'(seolhyun kim, irene kim and jisoo kim) in my student table and I have created a student_name column that consists of the students' full name. When I try to get a specific student's name using regular expression and the set list [], I do not get the name i want out of these 3.

When I use "where student_name regexp '[seolh] kim'; ", Irene kim and Jisoo kim names gets returned instead of Seolhyun kim. If I just use "where student_name regexp '[se] kim'; ", Irene kim's name gets returned and if i use Seolhyun's full first name "where student_name regexp '[seolhyun] kim'; ", all of the 3 women's name gets returned.

SELECT roll_number, student_name
FROM student
WHERE student_name REGEXP '[SE] KIM';

`[]` in regex defines a character class, it means "match a character from the list supplied". So `[seolh] kim` will match any string that has `s` or `e` or `o` or `l` or `h` followed by a space and `kim` — VLAZ, Oct 06 '19 at 18:44
Possible duplicate of [Reference - What does this regex mean?](https://stackoverflow.com/questions/22937618/reference-what-does-this-regex-mean) — VLAZ, Oct 06 '19 at 18:45

score 1 · Accepted Answer · answered Oct 06 '19 at 18:46

1

[seolh] means one of the letters s,e,o,l, or h.

So [seolh] kim matches a person with last name kim and a first name that ends in one of these letters.

Irene ends with e, and Josoo ends with o so those match. Seolhyun ends with n so it does not match.

You might want (seolh\S*) kim

answered Oct 06 '19 at 18:46

Taemyr

3,201
13
25

"*You might want `(seolh\S*) kim`*" I find little value to that versus a plain `LIKE 'seolh% kim` sure, there are semantic differences but I am not entirely convinced a regex is really needed here. – VLAZ Oct 06 '19 at 18:51
Thank you for helping me, now I understand it and I am currently learning about MySQL database so I learnt about this – mr3oh5 Oct 06 '19 at 18:52
1

@mr3oh5: take care that if you write `n kim`, jong-un kim could also be a possible result. – Casimir et Hippolyte Oct 06 '19 at 18:55

score 1 · Answer 2 · answered Oct 06 '19 at 19:16

I would recommend starting with:

WHERE student_name REGEXP '(seolhyun|irene|jisoo) KIM';

This checks for the three first names. If you want a full-string match (which seems likely), then use:

WHERE student_name REGEXP '^(seolhyun|irene|jisoo) KIM$';

If you only want to match one name, then use =:

WHERE student_name = 'seolhyun KIM'

For more than one name, here are two suggestions:

WHERE student_name IN ('seolhyun KIM', 'irene KIM')
WHERE student_name REGEXP '^(seolhyun|irene) KIM$';

I am getting weird answers with regularexpression with setlist on mysql

2 Answers2