string search in MySQL Query

Question

My MySQL field having skills field(string) like,

1)php,mysql
2)java
3)java,c

I need to select the column with skills field condition. Condition may be Like,

skills="php"    // o/p => 1st column
or
skills = "mysql,c"   // o/p => 1st and 3rd column

How I can write Query.? Here im using PHP.

KarSho, any change you apply the First normal form and get your skill field in other table? — medina
– medina, Commented May 6, 2013 at 5:49
@medina ya, thanks for your !dea. But, i designed DB fully. Now i can't change. — KarSho
– KarSho, Commented May 6, 2013 at 5:51

peterm · Accepted Answer · 2013-05-06 05:49:07Z

3

One way to do it

SELECT *
  FROM table1
 WHERE skills LIKE '%mysql%' 
    OR skills LIKE '%c%'

SQLFiddle

It would be much easier if you'd normalized your data

answered May 6, 2013 at 5:49

peterm

93k16 gold badges157 silver badges162 bronze badges

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

5 Comments

KarSho Over a year ago

My search condition may having 'n' content

peterm Over a year ago

@KarSho What do you mean by '...having 'n' content'?

peterm Over a year ago

@KarSHo What should happen when search condition is empty? Show all records or doesn't show anything?

peterm Over a year ago

@KarSho Well then in your php code if search condition is empty don't add WHERE clause, if it's not empty split it and build up your WHERE clause with as many OR's as you have keywords

Matthew Over a year ago

potentially unlimited number of OR clauses?

Community · Accepted Answer · 2017-05-23 11:49:16Z

1

Use a procedure like this one:

Split comma separated values from one column to 2 rows in the results. MySQL

To split your csv skills field into a temp table with rows for each skill

Then do the same thing with your search parameters (which I assume is also csv string passed in)

Then simply apply a JOIN over the two results and get the DISTICT Id.

there are many solutions for splitting a comma separated string in MySQL (though it's not particularly good at this task)... if you can't properly normalize the data then you can't very well complain about inefficient query routines.

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered May 6, 2013 at 20:31

Matthew

10.5k5 gold badges53 silver badges107 bronze badges

Collectives™ on Stack Overflow

string search in MySQL Query

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related