CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
Gaurav Sachdeva
Gaurav Sachdeva

Reputation: 75

Use data separated by delimiters in Sql Procedure

I have a Sql Procedure that receives a string as a parameter. Now the task that I need to perform is that the parameter contains data that is separated by two different kinds of delimiters i.e ":" and ","

There are two columns in the DB Id and Value.

     sample Data: "10:0,11:1,12:3,13:4,15:5,16:6"
     In This case          Ids are: 10,11,12,13,14,15,16
     and their respective  values are: 1,2,3,4,5,6

Now I want to insert these values in DB. Can you please suggest a solution?

Upvotes: 0

Views: 690

Answers (3)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

You can use following function:

CREATE FUNCTION Split (
      @InputString                  VARCHAR(8000),
      @Delimiter                    VARCHAR(50)
)

RETURNS @Items TABLE (
      Item                          VARCHAR(8000)
)

AS
BEGIN
      IF @Delimiter = ' '
      BEGIN
            SET @Delimiter = ','
            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
      END

      IF (@Delimiter IS NULL OR @Delimiter = '')
            SET @Delimiter = ','

      DECLARE @Item                 VARCHAR(8000)
      DECLARE @ItemList       VARCHAR(8000)
      DECLARE @DelimIndex     INT

      SET @ItemList = @InputString
      SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      WHILE (@DelimIndex != 0)
      BEGIN
            SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
            INSERT INTO @Items VALUES (@Item)

            -- Set @ItemList = @ItemList minus one less item
            SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
            SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      END -- End WHILE

      IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
      BEGIN
            SET @Item = @ItemList
            INSERT INTO @Items VALUES (@Item)
      END

      -- No delimiters were encountered in @InputString, so just return @InputString
      ELSE INSERT INTO @Items VALUES (@InputString)

      RETURN

END -- End Function
GO

CREATE TABLE #Test
(
    Item NVARCHAR(1000)
)

INSERT INTO #Test
SELECT * FROM Split('10:0,11:1,12:3,13:4,15:5,16:6', ':')

SELECT f.* FROM #Test t
CROSS APPLY Split(t.Item, ',') f

DROP TABLE #Test

Upvotes: 0

mohan111
mohan111

Reputation: 8865

IF OBJECT_ID('tempdb..#Test') IS NOT NULL 
DROP TABLE #Test
GO

CREATE TABLE #Test(ID INT,Val INT)

DECLARE @t table (val varchar(50))
INSERT INTO @t (val)values ('10:0,11:1,12:3,13:4,15:5,16:6')


;WITH CTE AS (
SELECT   
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT   
         CAST ('<M>' + REPLACE([val], ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  @t) AS A CROSS APPLY String.nodes ('/M') AS Split(a))
     INSERT INTO #Test 
     select SUBSTRING(String,0,CHARINDEX(':',String)),REVERSE(SUBSTRING(reverse(String),0,CHARINDEX(':',reverse(String)))) from cte 

     select * from #test

Upvotes: 2

Arunprasanth K V
Arunprasanth K V

Reputation: 21931

You can use the following function for that, it will handle your two delimiters for spli

CREATE FUNCTION dbo.MultipleSplitStrings
(
   @List       NVARCHAR(MAX),
   @Separator1 Varchar(100),
   @Separator2 Varchar(100)
)
RETURNS  TABLE 
AS

   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(REPLACE(@List, ISNULL(@Separator1,''), '</i><i>') , ISNULL(@Separator2,''), '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );

GO  

Select * From dbo.MultipleSplitStrings ('10:0,11:1,12:3,13:4,15:5,16:6',',',':')

Result :

item 
10
0
11
1
12
3
13
4
15
5
16
6

Upvotes: 1

Related Questions