like operator issue

I have a 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 c开发者_如何学Pythonan I do that?

in order to handle all possible cases (beginning of string, end of string, between 2 delimiters) you would need the inefficient (can't use an index)

SELECT *
FROM   dom
WHERE  CONCAT(',', skills, ',') LIKE '%,C,%'
       AND CONCAT(',', skills, ',') LIKE '%,C++,%' 

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

A separate skills table and skills/person matrix table is much more efficient to search and easier to work with (e.g. removing an individual skill)

If you need a query that would bring back records in doms who match both C and C++ (assuming the same schema as jimmy_keen's answer) this could be done with a self join to get the intersection, the intersect operator, or using other relational division techniques such as

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 

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).

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.