CODEWITHSUNDEEP
sqlsql-servert-sqlsql-likecontains
Michael
Michael

Reputation: 2657

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?

Upvotes: 0

Views: 164

Answers (2)

Ilyes
Ilyes

Reputation: 14928

I think you're looking for

SELECT *
FROM
(
  VALUES
  (1,       'ABC',        'ABC DEF'),
  (2,       'XYZ',        'WERQ'),
  (3,       'MNB',        'MNB RTW'),
  -- for col2 like col1
  (4,       'HI MNB RTW S',        'MNB RTW')
) T(Id, Col1, Col2)
WHERE  CONCAT(' ', Col1, ' ') LIKE CONCAT('% ', Col2, ' %') 
       OR
       CONCAT(' ', Col2, ' ') LIKE CONCAT('% ', Col1, ' %');

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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, ' %')

Upvotes: 1

Related Questions