3

I need an efficient way to search multiple sub string in same column of a table. Following is the sample table and inefficient query I create. Can any one help me to make it more dynamic.

id | column_2 | column_2
1  | 65,35    | 14,13,20
2  | 41,15,16 | 10,21,23
3  | 12,14,15 | 10,12,20

SELECT * FROM `table1` WHERE `column_2` LIKE '%10%' AND `column_2` LIKE '%23%';

These values will be lengthy and the and condition also will be dynamic in amount. So is there any efficient way to create query for remove and condition (having).

Improve this question
8
  • 2
    You have two column_2's. Should that actually be column_2 and column_3? Also, could you explain a bit about the problem? This table is begging to be split up into two or three tables. That would significant improve the speed of your queries. Do you have control over the database design? Commented Oct 4, 2014 at 19:31
  • 14
    Don't store comma separated values in a single column. The problem you have with the query stems directly from that bad design choice. Commented Oct 4, 2014 at 19:32
  • 4
    Instead of posting an answer i consider to upvote the comment by a_horse_with_no_name Commented Oct 4, 2014 at 19:36
  • 2
    Is there a tutorial that people follow which makes them store comma-separated values in columns? This is appearing day after day, and the solution is what @a_horse_with_no_name said. Just don't do it. Use tables to store relations. It is called relational database for a reason. What you did here is made it into a glorified text file. Commented Oct 7, 2014 at 13:20
  • 3
    @N.B.: what confuses me even more is that every time this comes up the answer is "I didn't design it this way, and I can't change it". I wonder why there are so many databases where this is done if nobody did it... Commented Oct 7, 2014 at 13:26

2 Answers 2

Reset to default
3
+50

I took the function SPLIT_STR from here: MYSQL - Array data type, split string,

Our data-set

Assume we have the following data set;

select * from a_table;
+----+-------------+
| id | column2     |
+----+-------------+
|  1 | 10,11,23    |
|  2 | 5,14,23     |
|  3 | 2,18        |
|  4 | 23,10,11    |
|  5 | 230,100,110 |
|  6 | 11,100      |
+----+-------------+
6 rows in set

The function

We then create the function (referenced above);

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

The final query

From there, we can do a simple query;

SELECT id from a_table WHERE SPLIT_STR(column2, ",", 1) IN (10,23)

This will give the following result set;

SELECT id from a_table WHERE SPLIT_STR(column2, ",", 1) IN(10,23);
+----+
| id |
+----+
|  1 |
|  4 |
+----+
2 rows in set

To add more numbers

To add more numbers, simply add to the IN() function - comma separated values.

A note

Deeply consider this

Don't store comma separated values in a single column. The problem you have with the query stems directly from that bad design choice. – a_horse_with_no_name

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

7 Comments

@hd: is it a problem of using ","? can i use another character to separate those values? Or are you recommending to avoid multi values from the column? because i'm ok to replace "," with another character. But avoid multi value on the column will make a major design change on my program.
And seems this query apply "OR" condition. is it possible to apply AND for this "REGEXP". Only if the column have 10 and 23 return the column. Currently either 10 or 23 is returning the column.
This regexp matches (e.g) "910". (,|^)x(,|$) seems to address this case.
@RandomSeed I'm not following, sorry! Sugunan the comma (",") is actually what's holding you back, it's bad design - look into normalisation. I'm looking into using an AND condition now.
@ʰᵈˑ SELECT "910" REGEXP "10(,|$)"; wrongly returns TRUE, whereas SELECT "910" REGEXP "(,|^)10(,|$)"; returns FALSE as expected. Therefore the pattern to use is (,|^)x(,|$).
|
2

To search In a mysql tables structure you can use either a way

  1. To search with like operator using %search string%

  2. You can use mysql built in function FIND_IN_SET()

 SELECT  *
 FROM    tableName
 WHERE   FIND_IN_SET('10', column name)  AND
 FIND_IN_SET('23', column name)
  1. Third way you can use is to create a string from your programming code by appending search keywords, to search for that corresponding sequence in the table.
Improve this answer

5 Comments

using multiple "and" is the one I wanted to avoid. Here same applies again.
Did you try using 3rd way
Do u hv to do it client side or server side?
These values will come from html multiselect box as array. And php will compile the query string. But I feel better to avoid the dynamic number of and condition. Because it is going to have 12 multi select box with 9 values per each. So total possible and will be 12x9
Yeah i got ur point so its better you use last option,make a string by appending the vales and then srarch for its occurance it will not only redure your query time but also search efficiently,you can seach that appened string in Find-in-set it will be perfect.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.