Click here to Skip to main content
14,297,435 members
Rate this:
Please Sign up or sign in to vote.
I have Stored Procedure that takes input as string containing Grid view records. Rows are identified by ; separator & columns are identified by , separator. Sample string data

T001,20 Sep 2015,9/20/2015 22:00:00,9/20/2015 23:00:00;T002,20 Sep 2015,9/20/2015 22:00:00,9/21/2015 06:00:00;T003,20 Sep 2015,9/21/2015 01:00:00,9/21/2015 06:00:00


I am using Split Function (refer http://www.aspdotnet-suresh.com/2013/07/sql-server-split-function-example-in.html[^] and http://www.aspsnippets.com/Articles/Split-function-in-SQL-Server-Example-Function-to-Split-Comma-separated-Delimited-string-in-SQL-Server-2005-2008-and-2012.aspx[^] But that works on Single Delimiter char.

using
SELECT * FROM dbo.Split(@s, ';') 
I got 3 rows as
T001,20 Sep 2015,9/20/2015 22:00:00,9/20/2015 23:00:00;
T002,20 Sep 2015,9/20/2015 22:00:00,9/21/2015 06:00:00;
T003,20 Sep 2015,9/21/2015 01:00:00,9/21/2015 06:00:00
Now i want to split again by , separator. So how to use this for two delimiter char.

Thanks in advance......
Posted
Updated 9-Sep-15 20:23pm
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

Hi Umesh

AS per told you you split first all records and get output like this

T001,20 Sep 2015,9/20/2015 22:00:00,9/20/2015 23:00:00;
T002,20 Sep 2015,9/20/2015 22:00:00,9/21/2015 06:00:00;
T003,20 Sep 2015,9/21/2015 01:00:00,9/21/2015 06:00:00


so this split data store in temp table and after split this temp table data row by row

i hope by using this you will get your solution!
   
Comments
Umesh AP 10-Sep-15 4:45am
   
Thanks Uttam, now working as per your suggestion.
Rate this:
Please Sign up or sign in to vote.

Solution 2

It can be do it that way. instead of using two delimiter in one query you can use it into two places.
CREATE FUNCTION [dbo].[SPLIT] 
   (  @DELIMITER VARCHAR(5), 
      @LIST      VARCHAR(MAX) 
   ) 
   RETURNS @TABLEOFVALUES TABLE 
      (  ROWID   SMALLINT IDENTITY(1,1), 
         [VALUE] VARCHAR(MAX) 
      ) 
AS 
   BEGIN
    
      DECLARE @LENSTRING INT 
 
      WHILE LEN( @LIST ) > 0 
         BEGIN 
         
            SELECT @LENSTRING = 
               (CASE CHARINDEX( @DELIMITER, @LIST ) 
                   WHEN 0 THEN LEN( @LIST ) 
                   ELSE ( CHARINDEX( @DELIMITER, @LIST ) -1 )
                END
               ) 
                                
            INSERT INTO @TABLEOFVALUES 
               SELECT SUBSTRING( @LIST, 1, @LENSTRING )
                
            SELECT @LIST = 
               (CASE ( LEN( @LIST ) - @LENSTRING ) 
                   WHEN 0 THEN '' 
                   ELSE RIGHT( @LIST, LEN( @LIST ) - @LENSTRING - 1 ) 
                END
               ) 
         END
      RETURN 
   END
   GO

And then create a temp table or table variable to store the output to further split.
Declare @Outputval as nvarchar(max)
CREATE table #temptable
(
	RowID int,
	OutputVal nvarchar(max)
)
Insert into #temptable 
   select * from dbo.SPLIT(';','T001,20 Sep 2015,9/20/2015 22:00:00,9/20/2015 23:00:00;T002,20 Sep 2015,9/20/2015 22:00:00,9/21/2015 06:00:00;T003,20 Sep 2015,9/21/2015 01:00:00,9/21/2015 06:00:00')

select @Outputval = OutputVal from #temptable

select * from dbo.SPLIT(',', @Outputval)

Query can be more simplify, i just try to give you idea.
Thanks
   
Comments
Umesh AP 10-Sep-15 4:45am
   
Thanks Schatak for reply, But I have to use previously defined Split function only so working as per Uttam's suggestion.
Schatak 10-Sep-15 6:05am
   
Split function can be anything i just described the solution. you can your own split function, it does not make any difference. :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100