0

I need to search value starting with given string in comma separated values.
Example,
I have '1_2_3_4_5_6, 1_2_3_4_5_8, 1_2_3_6_5_8' in my column. I can search for rows with exact value using
select * from table where find_in_set('1_2_3_4_5_6',column)

But how to search, if starting part of the string is given? something like this:
select * from table where find_in_set('1_2_3%',column) ?

shreyas d
  • 774
  • 1
  • 4
  • 16
  • 1
    So, you have a column which contains e.g. `1_2_3_4_5_6` and/or `1_2_3_4_5_6, 1_2_3_4_5_8, 1_2_3_6_5_8`? I'm not sure about your data structure. If the column contains multiple values, do you need only the ones matching your search string or is it okay to retun the complete row? – Alex Aug 24 '18 at 10:47
  • I need to get the rows if it fits the seach criteria.. I have updated the question..tell if anything is not clarified – shreyas d Aug 24 '18 at 10:54
  • I am not sure but I think this is not possible with find_in_set. You probably can try to go for a regex solution but this is not a good approach :/. Something like https://stackoverflow.com/a/5033073/3223157 – Xatenev Aug 24 '18 at 10:56
  • 3
    You should really normalise your data structure and put those values into a separate table in separate rows, then the SQL language can help you tremendously, that's what it's for. Dealing with this on the level of string manipulation is a bad and tedious approach. – deceze Aug 24 '18 at 11:00

3 Answers3

1

If I understand you correctly (I'm still not sure I do), you could just use:

SELECT * FROM table WHERE column LIKE '%1_2_3%';

This would give you columns where the value is like:

1_2_3_4_5_5
1_4_5_, 1_2_3_4_5_, 6_7

and so on.

But you should really normalize your tables. This is important for good queries and performance wise also important.

According to @Xatenev suggestions, if you really like only the values and the row of each matching row, this won't work so well and will be a lot of overhead. This are the steps that I would perform:

  1. Split all CSV columns into multiple rows (this is a hack and a performance killer, I found some working solution but did not test it, see here): Pseudo Code: SELECT ID, SPLIT(',', column) AS entries FROM table (NOT WORKING)
  2. Filter the new virtual table to select only rows that match the prefix (SELECT * FROM virtual_table WHERE find_in_set("1_2_3%, entries) ORDER BY ID)
  3. Concatenate the matching entries back into a list for each ID. e.g. SELECT ID, GROUP_CONCAT(entries SEPARATOR ', ') FROM filtered_table GROUP BY ID
  4. Do something

The unknown part is the beginning with the split in multiple rows. There are a lot of possible solutions all with their own drawbacks or advantages. Be aware that this will always (regardless of the selected method) will cost a lot of performance.

ADDITIONAL NODE:

It could be adventures in your situation, that you get each row matching your search string like in my first example and filter them in memory. This might be faster than doing this in MYSQL.

Alex
  • 1,857
  • 3
  • 36
  • 51
  • His values are comma seperated. % won't work with LIKE here obviously because it would act on the whole column instead of just the comma seperated values each on their own – Xatenev Aug 24 '18 at 11:18
  • Why should it not? When the values in the column (not columns) are comma separated, `LIKE` will catch any row that contains any matching string (even if they are in the middle or end of the separated list). You can test with `SELECT * FROM (SELECT "1_4_5_, 1_2_3_4_5_, 6_7" as col) t WHERE t.col LIKE "%1_2_3%"`. If he only likes the values matching, which I asked, this won't work. But if the row is enough for him (how I understand his comment), this will. – Alex Aug 24 '18 at 11:22
  • Yea and thats not what he wants. As you can see in his example he wants something similar to `find_in_set("1_2_3%")` - that means he wants to find a value in the comma seperated string starting with 1_2_3 and ending with anything (wildcard). Thats not what your code does. (I am just judging from his example code: `find_in_set("1_2_3%")`. Maybe I misunderstand him though and you're right. – Xatenev Aug 24 '18 at 11:25
  • I altered my response according to your understanding. Maybe this can help OP. – Alex Aug 24 '18 at 11:47
0

you can try with 'REGEXP'

If you want to match data with subtring, please try this

SELECT * FROM `table` WHERE `column` REGEXP '[, ]?1_2_3[^,]*,?' ;

Or

If you want to exact start match, please try this

SELECT * FROM `table` WHERE `column` REGEXP '[, ]?1_2_3[^,]*,?' AND `column` NOT REGEXP '[^, ]1_2_3[^,]*,?' ;
Sonam Tripathi
  • 183
  • 1
  • 12
  • This will only find one matching entry. This is no better solution than using `LIKE '%1_2_3%'`. Also, regex on SQL is cost intensive. – Alex Aug 24 '18 at 12:02
  • Yeah!! I agree with you regex on SQL is cost intensive. However if we use LIKE '%1_2_3%' , then it wont search for starting exact matching string as requirement given, it will search string containing 1_2_3 , rather than string starting with 1_2_3. That is why I tried with REGEX. – Sonam Tripathi Aug 28 '18 at 04:54
0

I was able to solve it with no-regex. Sonam's Answer is correct as well.

SELECT * from table WHERE CONCAT(',', columnname, ',') like '%,1_2_3%'
shreyas d
  • 774
  • 1
  • 4
  • 16