Write a stored procedure which takes input string and split them by characters

Question

I am trying to create a stored procedure in SQL Server which will be used from C# Entity Framework.

My main focus is take a input of long string text then split that data by characters and return list of values of matching data.

In detail:

- is separator between name of data type and its value
: is separator between Type and ASIN
, is separator between two different value

I want to get List of data filtered by ASIN and Type from this stored procedure. I am getting full text string in the @DataString variable but I don't know how I can split my text and run SELECT to return all of data.

Any idea to do it? Ask any question you may have.

Example of long text string:

Type-1:ASIN-NsQf8,ASIN-YhQfu,ASIN-dpQf9,ASIN-rsWf3

The unfinished SQL code:

CREATE PROCEDURE dbo.lk_GetMatchingDataOfThirdparty 
     @DataString VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * 
    FROM ThirdPartyData 
    WHERE ASIN = '@value_get_from_string' 
      AND Type = '@value_get_from_string'
END

Is there a reason you can't split the string, in the program making the query, into the `Type` and a table-valued-parameter for the `ASIN` values? — Andrew Morton, Aug 24 '18 at 12:39
I dont know how to split them from string also how to return each data of each ASIN at a time as a List. Since there multiple ASINs — john Cogdle, Aug 24 '18 at 12:41
Yes. I am calling from C# Entity Framework. I want to input that string and need List of all data matching with ASINs and Type. Anyone can solve issue? — john Cogdle, Aug 24 '18 at 12:44
How did all the parts of the string get put together? Perhaps if you showed us the C# code which is going to call the stored procedure we could help. — Andrew Morton, Aug 24 '18 at 12:47
I am converting my List of data to string then wanted to pass to stored procedure as shown in example string text — john Cogdle, Aug 24 '18 at 12:52
OK, you are making it more difficult by putting all the data into one string. What you need is one variable for the "Type", and one table-valued-parameter for the "ASIN" values. This should help: [How to pass table value parameters to stored procedure from .net code](https://stackoverflow.com/q/5595353/1115360), but don't use AddWithValue, as explained in [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — Andrew Morton, Aug 24 '18 at 12:58

score 1 · Accepted Answer · answered Aug 24 '18 at 13:10

Use a split function:

CREATE FUNCTION [dbo].[split](
        @delimited NVARCHAR(MAX),
        @delimiter NVARCHAR(100)
      ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
      AS
      BEGIN
        DECLARE @xml XML
        SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

        INSERT INTO @t(val)
        SELECT  r.value('.','nvarchar(MAX)') as item
        FROM  @xml.nodes('/t') as records(r)
        RETURN
      END
GO

DECLARE @DataString VARCHAR(MAX);

SET @DataString ='Type-1:ASIN-NsQf8,ASIN-YhQfu,ASIN-dpQf9,ASIN-rsWf3'


;WITH cte as (
SELECT a.id as [1], b.id as [2], c.id as [3], c.val
FROM (
   SELECT * FROM dbo.split(@DataString, ':')
) a
CROSS APPLY dbo.split(a.val,',') b
CROSS APPLY dbo.split(b.val,'-') c
),
typecte as (
   select b.val as [TypeValue]
   from cte a
      inner join cte b
      ON a.[1] = b.[1] 
      AND a.[2] = b.[2]
      AND a.[3]+1 = b.[3] -- Next value
   WHERE a.val='Type'
),
asincte as (
   select b.val as [ASINValue]
   from cte a
      inner join cte b
      ON a.[1] = b.[1] 
      AND a.[2] = b.[2]
      AND a.[3]+1 = b.[3] -- Next value
   WHERE a.val='ASIN'
)
SELECT *
FROM ThirdPartyData
WHERE [ASIN] IN (SELECT [ASINValue] FROM asincte)
AND [Type] IN (SELECT [TypeValue] FROM typecte)

Conversion failed when converting the nvarchar value 'Type' to data type tinyint. i am getting this error. I think need to convert Type? — john Cogdle, Aug 24 '18 at 13:31

score -1 · Answer 2 · answered Aug 24 '18 at 12:44

An oft overlooked way of doing coding that isn't really relational database related is a SQL CLR. String manipulation is a good example of something a SQL CLR could handle much better than a SQL script. What you could do in the above example is call a SQL CLR function that does the string manipulation on the long string text to return the values you need then plug those variables into your SELECT statement.

Write a stored procedure which takes input string and split them by characters

2 Answers2