match comma separated values in a string with table-CodePudding

As part of a stored procedure I receive a string of names separated by commas. The names can be first name, last name, full name or something in between.

How can I find out if the name exists in my table?

DECLARE @NameString
SET @NameString = Max,John World,Miller,Peter F, Will

Table:

ID  Name
1   10_Max_Power
2   11_Tim_Miller
3   87_John_World
4   12_Peter_Foo
5   3123_Tom_Miller
6   323_Jessica_Bar

Desired result:

ID  Name
1   10_Max_Power
2   11_Tim_Miller
3   87_John_World
4   12_Peter_Foo
5   3123_Tom_Miller

CodePudding user response：

Ideally, don't pass a delimited parameter, use a table type parameter. Then you can do something like this:

DECLARE @Names table (Name nvarchar(100));
INSERT INTO @Names (Name)
VALUES('Max'),
      ('John World'),
      ('Miller'),
      ('Peter F'),
      ('Will');

SELECT YT.ID,
       YT.Name
FROM dbo.YourTable YT
WHERE EXISTS (SELECT 1
              FROM @Names N
              --Rather than inline the REPLACE you could add Computed Column
              --to your table type variable/parameter.
              WHERE YT.Name LIKE CONCAT('%',REPLACE(N.Name,' ','[_]'),'%'));

If you "must" use a delimited string then you can use STRING_SPLIT (I assume you're using a fully supported version of SQL Server as there's nothing to say you aren't):

DECLARE @NameString nvarchar(4000)
SET @NameString = N'Max,John World,Miller,Peter F, Will';

SELECT YT.ID,
       YT.Name
FROM dbo.YourTable YT
WHERE EXISTS (SELECT 1
              FROM STRING_SPLIT (@NameString,',')
              WHERE YT.Name LIKE CONCAT('%',TRIM(REPLACE(N.Name,' ','[_]')),'%')); --TRIM because Will has a leading space

I do, however, also recommend fixing your design. A "name" with a value of 10_Max_Power is breaking normalisation rules. Clearly the 10 is something else other than the name. It's also clear you want to find people who's Forename or Surname might be the value. Really, therefore, your Name column should be at least 3 column; Prefix (or whatever the number represents), Forename and Surname (and optionally a column to Middle Name(s)).