CODEWITHSUNDEEP
t-sqlsubstringcharindex
marky
marky

Reputation: 5068

SQL WHERE condition that one field's string can be found in another field

Here's some sample data

    ID | sys1lname | sys2lname
------------------------------------
    1  | JOHNSON   | JOHNSON
    2  | FULTON    | ANDERS-FULTON
    3  | SMITH     | SMITH-DAVIDS
    4  | HARRISON  | JONES

The goal is to find records where the last names do NOT match, BUT allow when sys1lname can be found somewhere within sys2lname, which may or may not be a hyphenated name. So from the above data, only record 4 should return.

When I put this (SUBSTRING(sys2lname, CHARINDEX(sys2lname, ccm.NAME_LAST), LEN(sys1lname))) in the SELECT statement it will properly return the part of sys2lname that matches sys1lname.

But when I use that in the WHERE clause

WHERE 1=1
    AND sys1lname <> sys2lname
    OR sys1lname not in ('%' + (SUBSTRING(sys2lname, CHARINDEX(sys1lname, sys2lname), LEN(sys1lname))))

the records with hyphenated names are in the result set.

And I can't figure out why.

Upvotes: 0

Views: 38

Answers (1)

Thom A
Thom A

Reputation: 95564

Just use a NOT LIKE:

SELECT ID
FROM dbo.YourTable
WHERE sys2lname NOT LIKE '%' + sys1lname + '%';

If you could have a name like 'Smith' in sys1lname and 'BlackSmith' (or even 'Green-Blacksmith') in sys2lname and don't want them to match, I would use STRING_SPLIT and a NOT EXISTS:

SELECT ID
FROM dbo.YourTable YT
WHERE NOT EXISTS (SELECT 1
                  FROM STRING_SPLIT(YT.sys2lname,'-') SS
                  WHERE SS.[value] = YT.sys1lname);

Upvotes: 2

Related Questions