mysql select statement combination one to multiple

Question

best way to do this query

SELECT * FROM `table` WHERE  `column` like '%abc% %xcdb%' OR  `column` like '%abc% %dfhj%'

Here the first variable remains same but the second one changes for lets say an array of 10 to 20 So a better and faster way to repalce this iterative (column like)

table design

<table >
<tr>
    <td>id</td>
    <td>column</td>
    <td>other</td>
</tr>
<tr>
    <td>1;</td>
    <td>fsyd,**abc**,jgd,**xcdb**,sdfr</td>
    <td>tom</td>
</tr>
<tr>
    <td>2</td>
    <td>wer,tyuy,dhjd,dbhd</td>
    <td>john</td>
</tr>
<tr>
    <td>3;</td>
    <td>asy,gtyt,bnhhs,**abc**,bgy,trdcv,**dfhj**</td>
    <td>colin</td>
</tr>
     <tr>
    <td>4</td>
    <td>wer,**xcdb**,dhjd,**abc**,dbhd</td>
    <td>john</td>
</tr>
</table>

this query gives back the result of id (1 and 3) but not 4 as it checks for the sequence. this is a correct select statement and gives back the result i want but i am only asking for a better way to do this.

Could you add a short example of what you mean with a sample table and a desired result? — Joachim Isaksson, Feb 02 '13 at 12:06
Perhaps this Question link may be helpful to you. http://stackoverflow.com/questions/2514548/how-to-search-multiple-columns-in-mysql — Sumit Munot, Feb 02 '13 at 12:09
`SELECT * FROM table WHERE column like '%abc% AND (column like '%xcdb%' OR column like '%dfhj%');` ? assuming `'%xcdb%'` occurs more than `'%dfhj%'`. — जलजनक, Feb 02 '13 at 17:02

Matthew · Answer 1 · 2013-02-05T18:00:28.337

Based on what you are asking and the implication that order matters, you have two options:

Looping through a bunch of LIKE queries (as you are doing)
REGEX's

My understanding is there is a significant performance hit for regex in MySQL, but I cannot verify that firsthand. I will try and post an example of your query using a regex if you want it and I have a few minutes to test.

Should look something like this:

SELECT * FROM `table` WHERE  `column` REGEXP('.*abc.*((xcdb)|(dfhj).*)')

See proof in SQLFiddle here: http://sqlfiddle.com/#!2/8cd26/1

mysql select statement combination one to multiple

1 Answers1