Search code examples
sqlsql-servert-sqlsql-likecontains

t sql records that contain part of another record

Using SQL Server 2014. Is there a way to select records where the string value partially exists in another field?

e.g.:

RowID   Field1     Field2
1       ABC        ABC DEF
2       XYZ        WERQ
3       MNB        MNB RTW

From the above, I would want Rows 1 and 3 as they have ABC and MNB that matches.

SELECT RowID FROM MY TABLE
WHERE CONTAINS(Field1, Field2);

I have tried the above, however, this does not work as you cannot specify a 2nd field name in the CONTAINS function.

What am I missing?

Solution

  • You can use like:

    select t.*
from t
where field2 like concat('%', field1, '%')

    If you want only complete "words" to match -- well, you should fix your data model. You shouldn't be storing lists of things in a string. But, if you must, you can use delimiters:

    select t.*
from t
where concat(' ', field2, ' ') like concat('% ', field1, ' %')