Regular expression for MySQL database

Question

I have a table emp_skills which has emp_id and skill_id as two columns. Now,

SELECT * FROM emp_skills where skill_id REGEXP '^[2,3]$'

outputs emp_id's of those who have either 2 or 3 or both. What I want is emp_id's of those which have both 2 and 3 as their skill_id's. Skill_id is Integer

Example output of above query

EMP_ID     SKILL_ID
401        2   
405        2
401        3
405        3
407        3

Now what is I want is only first four rows as 407 does not have 2 as skill_id

what is the data type of column skill_id? and how are values stored in it? — Omesh
– Omesh, Commented Sep 11, 2012 at 7:20
'both 2 and 3 as their skill_id's': What do you mean exactly? Please add an example to your question. — user647772
– user647772, Commented Sep 11, 2012 at 7:20
It's probably '^(23|32)$' or '^(2[^0-9]*3|3[^0-9]*2)$', depending on the data format. — Alex Monthy
– Alex Monthy, Commented Sep 11, 2012 at 7:23
@BundleContext.. you dont need to use regex for this, try my answer below. — John Woo
– John Woo, Commented Sep 11, 2012 at 7:34

John Woo · Accepted Answer · 2012-09-11 07:34:50Z

3

from your phrase both 2 and 3 as their skill_id, i think you don't need regex for this. All you need are only count, group by and having

SELECT EMP_ID
FROM emp_skills
WHERE skill_ID IN (2,3)     
GROUP BY EMP_ID
HAVING COUNT(EMP_ID) = 2    -- the total number of skill_ID

you need to match the number of records with the total number of id supplied in the where clause.

SQLFiddle Demo

edited Sep 11, 2012 at 7:34

answered Sep 11, 2012 at 7:23

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Woo Over a year ago

@BundleContext.. you're welcome, i also added sqlfiddle demo.

Omesh · Accepted Answer · 2012-09-11 07:30:30Z

0

Assuming skill_id as INTEGER column, then this can be easily achieved by using IN clause:

SELECT * 
FROM emp_skills 
WHERE skill_id IN(2,3);

edited Sep 11, 2012 at 7:30

answered Sep 11, 2012 at 7:22

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

Collectives™ on Stack Overflow

Regular expression for MySQL database

2 Answers 2

SQLFiddle Demo

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related