MySQL like another field

Question

I have a table with two string columns: Url and ModelId. I need to return records for which Url contains ModelId, something like this:

SELECT Id, Url, ModelId WHERE Url like "%ModelId%"

possible duplicate of [Use LIKE %..% with field values in MySQL](http://stackoverflow.com/questions/4420554/use-like-with-field-values-in-mysql) — Richard Ayotte, Nov 22 '13 at 21:22

score 108 · Accepted Answer · answered Feb 03 '11 at 12:45

108

SELECT Id, Url, ModelId 
WHERE Url LIKE CONCAT('%', ModelId, '%')

answered Feb 03 '11 at 12:45

Michael Robinson

5

Technically you might want to escape the field in case it has % in it: LIKE CONCAT('%',REPLACE(field,'%','\%'),'%') – dlo May 21 '15 at 21:58

score 5 · Answer 2 · answered Oct 19 '16 at 20:00

5

You can not just concat the strings, you must also escape the field from % and _:

SELECT Id, Url, ModelId 
WHERE Url LIKE CONCAT('%', REPLACE(REPLACE(ModelId,'%','\%'),'_','\_'), '%'), '%')

answered Oct 19 '16 at 20:00

tal952

1

Great point. Question: should you also escape the '\' character? `REPLACE(REPLACE(REPLACE(ModelId,'\\','\\\\'),'%','\%'),'_','\_')` – drwatsoncode Aug 24 '20 at 20:32

score 3 · Answer 3 · edited Oct 01 '16 at 04:31

3

Here is the query:

SELECT Id, Url, ModelId WHERE Url LIKE CONCAT('%', ModelId, '%')

edited Oct 01 '16 at 04:31

Erba Aitbayev

answered Oct 01 '16 at 01:00

Mahee Gunturu

Sachin Shanbhag · Answer 4 · 2013-07-12T11:13:06.373

-1

SELECT Id, Url, ModelId from <table> WHERE Url like '%' + ModelId + '%'

edited Jul 12 '13 at 11:13

answered Feb 03 '11 at 12:44

Sachin Shanbhag

your query does not make sense. – Fr0zenFyr Jun 12 '13 at 13:21
1

since when does `+` work for string concatenation in MySQL? – But those new buttons though.. Nov 16 '15 at 21:34

4 Answers4