Is it possible to concatenate strings from data set items in find_in_set()?

Question

I'm using the following query:

set @var = 'A,B,C';
select from tbl_one where find_in_set(col_x, @var);

However, I have another table, which has column values based on @var as follows:

id    col_a
1     my.A
2     my.B
3     my.C

What I would like to do, is to use the same @var, but concatenate items in data set with 'my.' to be able to perform select using find_in_set(col_a, @var) which will be analogous to

select * from table_two where col_a in ('my.A', 'my.B', 'my.C')

Is it possible and how to do it?

This will help you to some extent https://stackoverflow.com/questions/5015403/mysql-find-in-set-with-multiple-search-string — murtuza hussain, Oct 29 '18 at 12:11

Madhur Bhaiya · Accepted Answer · 2018-10-29T12:20:53.987

1

You can remove the substring my. to the col_x column, using Replace() function, for comparison purpose. Note that it will not modify the original data.
Now, you can utilize the Find_in_set() function to look for a match.

Try the following:

SET @var = 'A,B,C';
SELECT FROM tbl_two 
WHERE FIND_IN_SET(REPLACE(col_x, 'my.', ''), @var);

If there is a chance that you may have my. substring in the middle/end of the col_x values as well, and we need to replace only from the beginning. We could use Trim() function in that case:

SET @var = 'A,B,C';
SELECT FROM tbl_two 
WHERE FIND_IN_SET(TRIM(LEADING 'my.' FROM col_x), @var);

edited Oct 29 '18 at 12:20

answered Oct 29 '18 at 12:10

Madhur Bhaiya

28,155
10
49
57

`replace` works! thanks a lot. (`concat` you suggested before edit didn't work) – Alexander Zhak Oct 29 '18 at 12:18
@AlexanderZhak yes I interpreted question incorrectly previously. Please check the updated answer. There is one more approach covering some edge cases. – Madhur Bhaiya Oct 29 '18 at 12:21

Is it possible to concatenate strings from data set items in find_in_set()?

1 Answers1