CODEWITHSUNDEEP
mysql
Rusty Rob
Rusty Rob

Reputation: 17173

how to find a comma with mysql find_in_set

is it possible to find a commma with find_in_set?

I tried:

select find_in_set('\,', '\,');

my use case is:

where find_in_set(r.tag, vfilterList)

here, vfilterList is a comma separated list provided as input to a stored procedure - and r.tag is the tag string in the table. (So I want to filter to only rows that have a tag that's in the vfilterList). However some tag strings have commas in them. (in vfilterList the commas in tags would be escaped?).

Something tells me i'm doing it wrong?

Upvotes: 1

Views: 1395

Answers (2)

Rusty Rob
Rusty Rob

Reputation: 17173

Ended up just replacing commas with pipes. (and doing the same for each tag within the filter list). It's reasonably accurate with false positives very unlikely for our use case.

where find_in_set(replace(r.tag,",","|"), vfilterList)

Upvotes: 0

Michael - sqlbot
Michael - sqlbot

Reputation: 179124

No, it isn't possible.

The documentation specifically points out that this doesn't work.

This function does not work properly if the first argument contains a comma (“,”) character.

http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set

The function is designed to return the ordinal position of a substring, delimited by commas. By definition, essentially, it would never find anything with a comma in it, since it's only considering the values between them.

Depending on what you're actually trying to accomplish, INSTR() or SUBSTRING_INDEX() might be useful.

Upvotes: 2

Related Questions