like operator issue

Question

I have dom table

select * from dom

dom table details:

id    name     skills
1     dom      c,c++

Here I want to retrieve query using like operator

select * from dom where skills like '%c,c++%'

Then I got the desired result.....that's not a problem.

Suppose I want to use the below query

`select * from dom where skills like '%C++,C%'`

I didn't get a result.

So i have to show details even if i search reverse order in database.

How can I do that?

Answer 1

Best answer would be to put your database into at least first Normal Form!

select * from dom where skills like '%C%' 

would work for this particular query though as clearly if it matches C++ it has to match C

the general approach would need to be

select * from dom where skills like '%C%'  or skills like '%C++%'

If you wanted to avoid the issue where you couldn't search for C without bringing back C++ records you would need to store the string as ',C,C++,' and search for '%,C,%' or handle all these possible cases (beginning of string, end of string, between 2 delimiters).

The above is only for completeness though. A separate skills table and skills/person matrix table avoids all this and is more efficient and easier to work with (e.g. removing an individual skill)

Edit: Just an addition to jimmy_keen's answer.

If you need a query that would bring back records in doms who match both C and C++ this could be as follows using his schema.

SELECT d.name
FROM dom d 
JOIN dom_skills ds ON (d.id = ds.id_dom) 
JOIN skills s ON (ds.id_skill = s.id)
WHERE s.name in ('C','C++')
GROUP BY d.id, s.id, d.name
HAVING COUNT(DISTINCT s.name) = 2

Answer 2

You need to use full text search or redesign your schema by extracting skills into a separate table and have M:N relation between Skill and Dom tables.

Answer 3

Skills column looks like something you might want to map with many-to-many relationship with your original "dom" table. For example:

SKILLS      DOM         DOM_SKILLS
id | name   id | name   id_dom | id_skill
---'-----   ---'-----   -------'---------
 1 | C       1 | dom        1  |  1 
 2 | C++                    1  |  2

This way, your query might look bit more complex...

SELECT d.name, s.name 
FROM dom d 
JOIN dom_skills ds ON (d.id = ds.id_dom) 
JOIN skills s ON (ds.id_skill = s.id)
WHERE s.name LIKE '%C%' OR s.name LIKE '%C++'

...but your schema will be easier to manage (think about duplicate entries, updating etc.. the normal form stuff, which is at the moment violated by your schema).