CODEWITHSUNDEEP
mysqlregexselectsql-like
compcentral
compcentral

Reputation: 1185

mySQL: Trouble with select query containing | symbol (LIKE and/or REGEXP)

My current query looks like this:

$query = "SELECT * FROM t WHERE (data LIKE '$findme') OR (data LIKE '%$findme|%') OR (data LIKE '%|$findme%')";

The data in this column contains items that are delimited by pipe symbols ("|") and I need the query to find all records that contain any occurrences of an item in this field. Additionally, if there's only one item in this column, there will not be a "|" symbol present.

EXAMPLE:

$findme = "12";

QUERY SHOULD MATCH:

13|23|12
12
12|23
3|12|42

QUERY SHOULD NOT MATCH:

123|32
34|123

I'm not sure if using REGEXP would make this easier, but if so, any solution is welcome. Thanks!

SQLFiddle Example: http://sqlfiddle.com/#!3/32afd/5

Upvotes: 2

Views: 1648

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270599

Using a combination of REPLACE() and FIND_IN_SET(), you can replace the | with commas, and locate your value $findme in the pipe-delimited set:

SELECT * 
FROM table
/* Replace | with , and search in the set */
WHERE FIND_IN_SET('$find_me', REPLACE(data, '|', ',')) > 0

Note that this only works if the delimited values in data do not contain commas.

In the long run, the appropriate solution to this is to separate out the delimited column data into another properly normalized one to many table.

Upvotes: 3

Related Questions