TSQL split comma delimited string

Question

I am trying to create a stored procedure that will split 3 text boxes on a webpage that have user input that all have comma delimited strings in it. We have a field called 'combined_name' in our table that we have to search for first and last name and any known errors or nicknames etc. such as @p1: 'grei,grie' @p2: 'joh,jon,j..' p3: is empty.

The reason for the third box is after I get the basics set up we will have does not contain, starts with, ends with and IS to narrow our results further.

So I am looking to get all records that CONTAINS any combination of those. I originally wrote this in LINQ but it didn't work as you cannot query a list and a dataset. The dataset is too large (1.3 million records) to be put into a list so I have to use a stored procedure which is likely better anyway.

Will I have to use 2 SP, one to split each field and one for the select query or can this be done with one? What function do I use for contains in tsql? I tried using IN win a query but cannot figure out how it works with multiple parameters.

Please note that this will be an internal site that has limited access so worrying about sql injection is not a priority.

I did attempt dynamic SQL but am not getting the correct results back:

    CREATE PROCEDURE uspJudgments @fullName nvarchar(100) AS
   EXEC('SELECT *
         FROM   new_judgment_system.dbo.defendants_ALL 
         WHERE  combined_name IN (' + @fullName + ')')
GO

EXEC uspJudgments @fullName = '''grein'', ''grien'''

Even if this did retrieve the correct results how would this be done with 3 parameters?

possible duplicate of [T-SQL stored procedure that accepts multiple Id values](http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values) — Andriy M, Oct 18 '12 at 16:04
I took a look at that and attempted using Dynamic SQL but the problem is I am not sure how to pass the value with the quotes or if you can use it with multiple comma delimited parameters. Also do you use IN or is there another function? — korrowan, Oct 18 '12 at 16:20
You don't want to use Dynamic SQL like that because its very susceptible to SQL Injection attacks. — RBarryYoung, Oct 18 '12 at 16:26
SQL injection will not be an issue. This is an internal application that 4 people will have access too. — korrowan, Oct 18 '12 at 16:27
I just answered an almost identical question, here: http://stackoverflow.com/questions/12958838/sql-server-2008-filter-on-big-list-passed-in/12959210#12959210 — RBarryYoung, Oct 18 '12 at 16:28
In any event, Dynamic SQL is not needed in this case. My answer at the link, shows how to implement one of the other techniques (string splitting) in the fastest way known in T-SQL. — RBarryYoung, Oct 18 '12 at 16:34
You could write a TVF turning your CSV list into a rowset (one item per row), using one of the methods suggested in the [accepted answer](http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values/43767#43767). Then you could select from that rowset in an `IN` clause: `... WHERE combined_name IN (SELECT value FROM dbo.SplitFunction(@fullname))`. No dynamic SQL required. — Andriy M, Oct 18 '12 at 16:35
RBarryYoung I will attempt to use that. I am going to have to call that for each individual paramater in the stored procedure I will write correct? If so will that still be the quickest way if it has to run 3 times? — korrowan, Oct 18 '12 at 16:36
@korrowan: My example at the link takes a parameter and returns a rowset, split by whatever character you tell it to (comma in this case). So if you have one `@parameter` with three comma-separated values in it, then you only call the split function once. If you meant that you have three separate `@Parameters`, that each have one or more comma-separated values in them, then yes, you either call the function three times, or else just concatenate them together with commas in-between and call it once. — RBarryYoung, Oct 18 '12 at 18:06
okay I got this to work but IN is the incorrect function for what I need. IN you need an exact match correct? I need to use IKE '%' + @fullName + '%'. How can I use LIKE with that function? For instance I need 'joh,jon,j.' to pull john,jon,jonathan etc. — korrowan, Oct 19 '12 at 04:01
Does this answer your question? [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) — Bill Tür stands with Ukraine, Feb 01 '22 at 10:06

score 3 · Answer 1 · edited May 23 '17 at 10:27

3

You may try use this to split string and obtain a tables of strings. Then to have all the combinations you may use full join of these two tables. And then do your select.

edited May 23 '17 at 10:27

Community

1
1

answered Oct 19 '12 at 07:23

Andrey Stukalin

5,328
2
31
50

score -1 · Answer 2 · edited Jul 09 '14 at 11:37

Here is the Table valued function I set up:

ALTER FUNCTION [dbo].[Split] (@sep char(1), @s varchar(8000)) 
RETURNS table
AS
RETURN (     
    WITH splitter_cte AS (       
        SELECT CHARINDEX(@sep, @s) as pos, 0 as lastPos       
        UNION ALL      
        SELECT CHARINDEX(@sep, @s, pos + 1), pos       
        FROM splitter_cte       
        WHERE pos > 0     
    )     
    SELECT SUBSTRING(@s, lastPos + 1,                      
        case when pos = 0 then 80000                      
        else pos - lastPos -1 end) as OutputValues     
        FROM splitter_cte   
        )
)

TSQL split comma delimited string

2 Answers2