How to select a Name in an List?

Question

I have this sample table

CREATE TABLE [dbo].[Person_Table]
(
    [Person] [nchar](100) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Person_Table] ([Person])
VALUES ('Ken, Brian, Mike,')
       ('Mikem, Briane, Kenny,')

If I want to look for Mike with this:

DECLARE @Person nvarchar(200)
SET @Person = 'Mike'

SELECT *
FROM [AdventureWorks2019].[dbo].[Person_Table]
WHERE Person LIKE '%' + @Person + '%'

Then I get the second row too. But it should not be. Is there are better way to get only the first row with Mike?

Result table:

Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64)

Answer 1

You've stored the data in a manner that's an anti-pattern. For example, you can't simply index your search. If normalised, however, you Can EASILY index the search.

For example...

CREATE TABLE [dbo].[person_list]
(
  id INT IDENTITY(1,1),
  list NVARCHAR(100)
);

INSERT INTO
  [dbo].[person_list] (list)
VALUES
  (N'Ken, Brian, Mike,')
  (N'Mikem, Briane, Kenny,')
;

CREATE TABLE [dbo].[person]
(
  id INT IDENTITY(1,1),
  person NVARCHAR(100),
  person_list_id INT
);

INSERT INTO
  [dbo].[person] (person, person_list_id)
VALUES
  (N'Ken', 1),
  (N'Brian', 1),
  (N'Mike', 1),
  (N'Mikem', 2),
  (N'Briane', 2),
  (N'Kenny', 2)
;

SELECT
  *
FROM
  person_list
INNER JOIN
  person
    ON person.person_list_id = person_list.id
WHERE
  person.person = N'Mike' 

If you MUST stick with your broken structure, you CAN do this...

SELECT
  *
FROM
  person_table
WHERE
  N', ' + person LIKE N'%, Mike, %' 
  -- Assumes the person column is ALWAYS terminated with ', ' as per your example

Which will perform like shit on larger tables, be a maintenence headache, and break if you ever get names like Mike, Jr

EDIT:

Or, tidying up a string_split() example (but having the exact same problems as the LIKE version...)

SELECT
  *
FROM
  person_table
CROSS APPLY
  string_split(replace(person_table.person, N', ', N',') N',') AS person
WHERE
  person.value = N'Mike' 

Answer 2

Please try the following solution.

Your data resembles a JSON array.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Person NVARCHAR(100));
INSERT INTO @tbl (Person) VALUES 
('Ken, Brian, Mike'),
('Mikem, Briane, Kenny');
-- DDL and sample data population, end

DECLARE @Person NVARCHAR(100) = 'Mike';

SELECT * 
FROM @tbl
WHERE @Person in (SELECT value FROM OPENJSON(CONCAT('["', REPLACE(Person,', ','","'), '"]')));

Output

+---+------------------+
| 1 | Ken, Brian, Mike |
+---+------------------+

Answer 3

A couple of options you can try.

You could use SQL Server's string_split function and use = instead of like

SELECT *
FROM Person_Table p
where exists (select * from String_Split(Replace(p.person,', ',','),',')s where s.value=@Person)

An even simpler way would be as follows, all depending on your real-world data as to whether it's suitable but add a separator character to both the searched expression and input expression:

DECLARE @Person nvarchar(100)
SET @Person = Concat('Mike',',')

SELECT *
FROM Person_Table
WHERE Concat(Person,',') LIKE Concat('%',@Person,'%')

Answer 4

This String_split seems to work. Can someone understand why I get the data for both year, although i just enter 2020?

 CREATE TABLE [dbo].[Person_Table]
    (
      Person nvarchar(500) NULL, 
      Name2 nvarchar(500), 
      Year_N int
    ) ON [PRIMARY]

INSERT INTO Person_Table (Person, Name2, [Year_N])
VALUES ('Ken,Brian,Mike,','A,B,','2000'),
       ('Mikem,Briane,Kenny,','C,D,','2020'),
       ('Dodo,Chris,Mike,','A,B,','2020');

-------------------------------------------------
DECLARE @Person NVARCHAR(100) = 'Mike',
        @Date int = 2020

SELECT *
FROM [AdventureWorks2019].[dbo].[Person_Table]
WHERE EXISTS 
              (Select * from string_split(Person,',') 
               WHERE value in (@Person))
              
OR EXISTS     (Select * from string_split(Name2,',') 
               WHERE value in (@Person))

AND Year_N = @Date

Answer 5

To filter in lists, append the separator both in the hay and the needle:

DECLARE @Person nvarchar(200)
SET @Person = 'Mike'

SELECT *
FROM [dbo].[Person_Table]
WHERE ', ' + Person + ' ' LIKE '%, ' + @Person + ', %'

Here's a corresponding fiddle: http://sqlfiddle.com/#!18/f64d30/2

A warning note, though: this approach will always yield a full table scan.

Answer 6

Just giving some possibilities for you and it is your choice what method you use
Used String_SPLIT() function to split the string and used a temp table to get the name filtered.

1st Way: You can use Temp, CTE, Derived Table to handle your data. It is up to you to decide which way is required as this shows one possibility:

 CREATE TABLE [dbo].[Person_Table]
 (
  [Person] [nchar](100) NULL
  ) ON [PRIMARY]

 INSERT INTO [dbo].[Person_Table] ([Person])
 VALUES ('Ken, Brian, Mike,')
 ,  ('Mikem, Briane, Kenny,')


 SELECT  RTRIM(LTRIM([Value])) AS  [Person]
 INTO #Temp_Data
 FROM [Person_Table]  
     CROSS APPLY STRING_SPLIT([Person], ',');  

 --Filter from what you need:

 SELECT * FROM #Temp_Data
 WHERE [Person]  = 'Mike'

2nd possibility:

Using CHARINDEX to find the start of the Exact Word you need to find and then filter by your Value. Value that you want to filter can be used with a variable as you don't need to hardcode it over and over again.

  SELECT
    A.Person
  FROM
 (
  SELECT   SUBSTRING( Person,  CHARINDEX('Mike,' ,[Person], 0 ),  
   LEN('MIKE'))AS  [Value] 
   , Person
  FROM [Person_Table]  

  ) AS A WHERE A.[Value]= 'Mike'