Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

spliting nested string Expand / Collapse
Author
Message
Posted Thursday, May 20, 2010 12:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, November 2, 2014 11:23 PM
Points: 324, Visits: 948
Hi all,

I want to split a string into table structure.

my string looks like '1:1,3,5,7,4:56,43,58,5:34,67r,234'

expected output is:

value1 value2
1 1
1 3
1 5
1 7
4 56
4 43
4 48
5 34
5 67r
5 234

where ever i find a ':' the previous digit should be value1 and value2 should be the rest of the comma separated values till it find next ':'. Remember to leave the last one for value1

i'm trying to modify the default function to split string
 CREATE FUNCTION [dbo].[fn_CSVToTable] ( @StringInput VARCHAR(8000) ,@Seprator Varchar(1))
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN

DECLARE @String VARCHAR(10)

WHILE LEN(@StringInput) > 0
BEGIN
SET @String = LEFT(@StringInput,
ISNULL(NULLIF(CHARINDEX(@Seprator, @StringInput) - 1, -1),
LEN(@StringInput)))
SET @StringInput = SUBSTRING(@StringInput,
ISNULL(NULLIF(CHARINDEX(@Seprator, @StringInput), 0),
LEN(@StringInput)) + 1, LEN(@StringInput))

INSERT INTO @OutputTable ( [String] )
VALUES ( @String )
END

RETURN
END

any guidelines / help are highly appreciated.

Thanks,
Regards,
Ami
Post #924829
Posted Thursday, May 20, 2010 5:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:40 AM
Points: 3,244, Visits: 5,008
I hope this will help;

Declare @StringInput varchar(100)
Declare @v varchar(100)
Declare @v1 varchar(100)
Set @StringInput = '199:141,3,5,7,4:56,43,58,5:34,67r,234'
Declare @OutputTable TABLE ( [String1] VARCHAR(10),[String2] VARCHAR(10))

Declare C1 Cursor for Select [value] from dbo.fnSplit(@StringInput,',')
Open C1
Fetch Next from C1 into @v
While @@Fetch_Status = 0
begin
if CharIndex(':',@v) > 0
begin
Insert into @OutputTable
Select Left(@v,CharIndex(':',@v)-1),Right(@v,Len(@v) - CharIndex(':',@v))

Set @v1 = Left(@v,CharIndex(':',@v)-1)
end
else
begin
Insert into @OutputTable
Select @v1,@v
end
Fetch Next from C1 into @v
end
Deallocate C1
Select * from @OutputTable




----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #924994
Posted Thursday, May 20, 2010 5:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:40 AM
Points: 3,244, Visits: 5,008
If you dont have fnSplit function, here is the code;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[fnSplit]
(@pString nvarchar(max),@pSplitChar char(1))
returns @tblTemp table (tid int,value varchar(1000))
as
begin
declare @vStartPosition int
declare @vSplitPosition int
declare @vSplitValue varchar(1000)
declare @vCounter int
set @vCounter=1
select @vStartPosition = 1,@vSplitPosition=0


set @vSplitPosition = charindex( @pSplitChar , @pString , @vStartPosition )
if (@vSplitPosition=0 and len(@pString) != 0)
begin
INSERT INTO @tblTemp
(
tid ,
value
)
VALUES
(
1 ,
@pString
)
return --------------------------------------------------------------->>
end
set @pString=@pString+@pSplitChar
while (@vSplitPosition > 0 )
begin
set @vSplitValue = substring( @pString , @vStartPosition , @vSplitPosition - @vStartPosition )
set @vSplitValue = ltrim(rtrim(@vSplitValue))
INSERT INTO @tblTemp
(
tid ,
value
)
VALUES
(
@vCounter ,
@vSplitValue
)
set @vCounter=@vCounter+1
set @vStartPosition = @vSplitPosition + 1
set @vSplitPosition = charindex( @pSplitChar , @pString , @vStartPosition )
end
return
end




----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #924995
Posted Thursday, May 20, 2010 6:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 2,873, Visits: 5,190
This one (without use of cursor or loops opr UDF) will perform and scale much better...

declare @pInput varchar(max) 
declare @n int
set @pInput = '1:1,3,5,7,4:56,43,58,5:34,67r,234'

set @pInput = ',' + @pInput + ','
select @n =LEN(@pInput)

set rowcount @n
select IDENTITY( int,1,1) as id into #tally
from sys.columns
set rowcount 0

declare @val1 varchar(500)
declare @res table (id int, vals varchar(1000), val1 varchar(500), val2 varchar(500))

insert into @res (id, vals)
select id
,SUBSTRING(@pInput,id+1,CHARINDEX(',',@pInput,id+1)-id-1) vals
from #tally
where SUBSTRING(@pInput,id,1) = ',' and id < LEN(@pInput)

update @res
set val1 = case when CHARINDEX(':', vals) > 0 then SUBSTRING(vals,0,CHARINDEX(':', vals)) else @val1 end
,val2 = case when CHARINDEX(':', vals) > 0 then SUBSTRING(vals,CHARINDEX(':', vals)+1,1000) else vals end
,@val1 = case when CHARINDEX(':', vals) > 0 then SUBSTRING(vals,0,CHARINDEX(':', vals)) else @val1 end

select val1, val2 from @res
drop table #tally




If your string to split expected to be very large, you can add clustered unique index on id column of #tally table and use # table instead of table variable for the result table. Otherwise, I think this code is fine as it is...


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #925035
Posted Friday, May 21, 2010 4:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, November 2, 2014 11:23 PM
Points: 324, Visits: 948
Hi,

Thanks for the inputs. both are working fine

thanks,
regards,
Ami
Post #925818
Posted Friday, May 21, 2010 6:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:40 AM
Points: 3,244, Visits: 5,008
Anamika (5/21/2010)
Hi,

Thanks for the inputs. both are working fine

thanks,
regards,
Ami


If the comma separated list is as long as 1000s of list items, it would be better to with tally table solution. But if it is limited to 10s or 20s. then its up to you both are feasible.


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #925886
Posted Friday, May 21, 2010 3:31 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 2:24 PM
Points: 1,519, Visits: 4,082
If you're really getting into the thousands of splits in a string, you're probably well into CLR territory.

WHAT?!?! SOMEBODY was gonna say it.



Seth Phelabaum
Consistency is only a virtue if you're not a screwup.

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #926308
Posted Saturday, May 22, 2010 11:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 35,612, Visits: 32,208
Atif Sheikh (5/21/2010)
Anamika (5/21/2010)
Hi,

Thanks for the inputs. both are working fine

thanks,
regards,
Ami


If the comma separated list is as long as 1000s of list items, it would be better to with tally table solution. But if it is limited to 10s or 20s. then its up to you both are feasible.


I have to disagree... limited row counts are never justification for writing something that isn't scalable.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #926448
Posted Wednesday, October 23, 2013 7:21 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 4:37 AM
Points: 632, Visits: 2,952
Ok, I know this thread is old but if PatternSplitCM was available when this was posted you could do this:

DECLARE @string varchar(100)='1:1,3,5,7,4:56,43,58,5:34,67r,234';

WITH prep1 AS
(
SELECT x.ItemNumber,
ROW_NUMBER() OVER (PARTITION BY x.ItemNumber
ORDER BY xx.ItemNumber) AS rnk,
x.Item,
xx.Item AS Item_sub
FROM ( SELECT * FROM dbo.PatternSplitCM(@string,'[^:]')
WHERE Item<>',') x
CROSS APPLY dbo.PatternSplitCM(x.Item,',') xx
WHERE x.Matched=1 AND xx.Matched=0
),
prep2 AS
(
SELECT ItemNumber+
CASE
WHEN rnk=MAX(rnk) OVER (PARTITION BY ItemNumber) AND
prep1.ItemNumber<>MAX(ItemNumber) OVER()
THEN 2 ELSE 0
END AS ItemNumber,
Item_sub,
CASE
WHEN rnk=MAX(rnk) OVER (PARTITION BY ItemNumber) AND
prep1.ItemNumber<>MAX(ItemNumber) OVER()
THEN 1 ELSE 0
END AS isParent
FROM prep1
)
SELECT p1.Item_sub, p2.Item_sub xx
FROM prep2 p1
JOIN prep2 p2 ON p1.ItemNumber=p2.ItemNumber
WHERE p1.isParent=1
AND p2.isParent=0
ORDER BY p1.Item_sub



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001

My blog
Post #1507859
Posted Friday, October 25, 2013 4:14 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:53 AM
Points: 3,422, Visits: 5,368
A bit of a different take on this, just for fun.

WITH SampleData (ID, MyString) AS
(
SELECT 1, '1:1,3,5,7,4:56,43,58,5:34,67r,234'
)
SELECT ID
,ItemNumber
,value1=CASE CHARINDEX(':', Item) WHEN 0 THEN NULL ELSE LEFT(Item, CHARINDEX(':', Item) - 1) END
,value2=CASE CHARINDEX(':', Item) WHEN 0 THEN Item ELSE SUBSTRING(Item, CHARINDEX(':', Item) + 1, 999) END
INTO #MyTable
FROM SampleData a
CROSS APPLY dbo.DelimitedSplit8K(MyString, ',') b

ALTER TABLE #MyTable ALTER COLUMN ID INT NOT NULL;
ALTER TABLE #MyTable ALTER COLUMN ItemNumber INT NOT NULL;
ALTER TABLE #MyTable ADD PRIMARY KEY(ID, ItemNumber);
ALTER TABLE #MyTable ADD value3 INT NULL;

DECLARE @VAL INT = 0, @ID INT;
UPDATE #MyTable WITH(TABLOCKX)
SET @VAL = CASE WHEN @ID = ID AND value1 IS NULL THEN @VAL ELSE value1 END
,value3 = @VAL
,@ID = ID
OPTION (MAXDOP 1);

SELECT ID, value1=value3, value2
FROM #MyTable;

GO
DROP TABLE #MyTable;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1508396
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse