# splitting the string dynamically in sql server 2008

Question

I have a string like this:

","","","41","20120627063343-0210600-41"

I wrote my query like this to split the above string

declare @s varchar(max)
set @s = '","","","41","20120627063343-0210600-41"'
select right(replace(@s,',',''),26) as output

And I am getting the following output

output
-------
20120627063343-0210600-41"

I want the output like this for the above string

YEAR         TIME         NO        ID
----         -----       ----      ---- 
2012-06-27    06:33:43    0210600   41

Help me!

Thanks and regards, Anand

Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Jun 29 '12 at 09:58

score 1 · Answer 1 · answered Jun 29 '12 at 10:26

I doubt this will be as fast as the substring methods suggested by others, but it might be more readable, regardless it's another option.

DECLARE @parsedString VARCHAR(255)
DECLARE @inputString VARCHAR(255)

SET @inputString = '","","","41","20120627063343-0210600-41"' 
SET @parsedString = REPLACE(REPLACE(REPLACE(REPLACE(@inputString, '-', '.'), '",', '.'), '"', ''), '...', '')

SELECT  PARSENAME(@parsedString, 1) as [Id],
        PARSENAME(@parsedString, 2) as [No],
        CAST(LEFT(PARSENAME(@parsedString, 3), 8) AS DATE) as [Year],
        STUFF(STUFF(RIGHT(PARSENAME(@parsedString, 3), 6), 3, 0, ':'), 6, 0, ':') as [Time]

score 0 · Answer 2 · answered Jun 29 '12 at 10:02

Using the SUBSTRING function, and assuming that the string example of 20120627063343-0210600-41 will always be the same fixed length, you can extract all the parts you need. Try this:

declare @s varchar(max)
declare @t varchar(max)
set @s = '","","","41","20120627063343-0210600-41"'
set @t = right(@s, 26) 

select left(@t, 4) + '-' + substring(@t, 5, 2) + '-' + substring(@t, 7, 2) AS [YEAR],
    substring(@t, 9, 2) + ':' + substring(@t, 11, 2) + ':' + substring(@t, 13, 2) AS [HOUR],
    substring(@t, 16, 7) AS [NO], 
    substring(@t, 24, 2) AS [ID]

score 0 · Answer 3 · answered Jun 29 '12 at 10:07

One possible solution:

declare @s varchar(max), @test varchar(26)
set @s = '","","","41","20120627063343-0210600-41"'
select @test = left(right(replace(@s,',',''),26),25)


select SUBSTRING(@test, 1, 4) + '-' + SUBSTRING(@test, 5, 2) + '-' + SUBSTRING(@test, 7, 2) as 'YEAR',
       SUBSTRING(@test, 9, 2) + ':' + SUBSTRING(@test, 11, 2) + ':' + SUBSTRING(@test, 13, 2) as 'TIME',
       SUBSTRING(@test, 16, 7) as 'NO',
       SUBSTRING(@test, 24, 2) as 'ID'

# splitting the string dynamically in sql server 2008

3 Answers3