brucezepplin brucezepplin - 4 months ago 6
SQL Question

How to match column partially contained in another column?

I have two columns such as:

col1 col2
---- ----
5qt exception for 5qt ammended
5qt exception for 5076 ammended
6d3 6d3 registered


I want to string match so that if the value of
col1
exists in
col2
, return col2.

So I get:

exception for 5qt ammended
6d3 registered


I have tried using
locate()
but not with much luck:

select col2 from mytable
where locate(col1,col2) = 1;


I have previously had this working where
col2
began with the value of
col1
i.e.

col1 col2
---- ----
5qt 5qt ammended
5qt 5076 ammended
6d3 6d3 registered


But it seems the fact the value of
col1
can appear in any position of
col2
is throwing
locate()
off the trail.

Answer

Your logic is okay. Just fix the comparison:

select col2
from mytable
where locate(col1, col2) >= 1;

Alternatively, you can use like:

where col2 like '%' || col1 || '%'
Comments