March 31, 2011 at 1:19 am
Hi Guys,
I've got a situation whereby I have a CSV variable passed to a stored procedure, however that CSV string also contains pipe (|) delimited values to denote individual records. Let me explain:
DECLARE @p VARCHAR(MAX)
SET @p = 'url1|title1|display1,url2|title2|display2,url3|title3|display3'
NOTE: the "probable" maximum number of separate items will be about 100.
My issue is that for each comma-separated "bit", I then need to split it out again into 3 separate pieces; URL, Title and Display in order to execute another stored procedure that inserts it into database.
I've read a lot about WHILE loops and CURSORS and I'd rather not use them, but I can't see how not to when trying to separate out the string and use the individual items when calling a separate procedure.
As always, many thanks for your replies!!
Cheers,
Kev.
March 31, 2011 at 1:46 am
Having posted my topic, I read about the PATINDEX function and so decided to try my hand using that and a WHILE loop (despite not wanting to). My results are below:
DECLARE @p VARCHAR(MAX)
,@url VARCHAR(255)
,@title VARCHAR(255)
,@display VARCHAR(255)
,@temp VARCHAR(1000)
SET @p = 'url1|title1|display1,url2|title2|display2,url3|title3|display3,'
WHILE LEN(@p) > 1
BEGIN
--this will contain the next chunk to process
IF PATINDEX('%,%',@p) > 0
SET @temp = SUBSTRING(@p,1,PATINDEX('%,%',@p))
ELSE
SET @temp = @p
SELECT @temp AS Temp
SET @url = SUBSTRING(@temp,1,CHARINDEX('|',@temp,1)-1) -- correct
SET @title = SUBSTRING(@temp,LEN(@url)+2,CHARINDEX('|',@temp,LEN(@url)+2)-(LEN(@url)+2))
SET @display = SUBSTRING(@temp,LEN(@url)+LEN(@title)+3,CHARINDEX(',',@temp)-(LEN(@url)+LEN(@title)+3))
SELECT @url AS URL, @title AS Title, @display AS Display
SET @p = SUBSTRING(@p,LEN(@temp)+1,LEN(@p))
END
If anyone does have a better solution, I'd love to hear your thoughts.
Thanks,
Kev.
March 31, 2011 at 2:11 am
Kev, this is ultra fast method of splitinng the strings.. its called DelimitedSplit8k.. i currently lost the link to it.. search this site for that, u ll be amazed by the speed it splits the strings 🙂
March 31, 2011 at 2:21 am
Cheers for the reply CC.
I did a search and here's the link: http://qa.sqlservercentral.com/Forums/Topic925149-338-1.aspx#bm933664.
This function does split out the strings delimited by a comma, but how do I then handle splitting them out further and calling the external procedure? I assume I'll need to call the function again, this time using a pipe as a delimiter, however I'll still need a WHILE loop right??
Cheers again,
Kev.
March 31, 2011 at 2:27 am
You can do it with a WHILE or you can utilize dynamic SQL to make each row a EXEC statement and execute it..
March 31, 2011 at 2:53 am
First, here is a delimited split function. If you search this site you will find other versions which may have better (or worse) performance. Please note the absence of loops, this makes the routine much faster than one the uses while loops or cursors.
USE [Sandbox]
GO
/****** Object: UserDefinedFunction [dbo].[DelimitedSplit] Script Date: 03/31/2011 02:48:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[DelimitedSplit] (
@pString varchar(max),
@pDelimiter char(1)
)
returns table
as
return
with
a1 as (select 1 as N union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1),
a2 as (select
1 as N
from
a1 as a
cross join a1 as b),
a3 as (select
1 as N
from
a2 as a
cross join a2 as b),
a4 as (select
1 as N
from
a3 as a
cross join a2 as b),
Tally as (select top (len(@pString))
row_number() over (order by N) as N
from
a4),
ItemSplit(
ItemOrder,
Item
) as (
SELECT
N,
SUBSTRING(@pDelimiter + @pString + @pDelimiter,N + 1,CHARINDEX(@pDelimiter,@pDelimiter + @pString + @pDelimiter,N + 1) - N - 1)
FROM
Tally
WHERE
N < LEN(@pDelimiter + @pString + @pDelimiter)
AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,N,1) = @pDelimiter --Notice how we find the delimiter
)
select
row_number() over (order by ItemOrder) as ItemID,
Item
from
ItemSplit
GO
Now, my code that will insert the values passed in as a string with double delimiters. Note that it will do the insert in a set-base manner that could eliminate the need for your second stored procedure. Please note, for simplicity of demonstration I used a table variable for the target table where the data is being inserted. You would replace that with your actual target table.
DECLARE @p VARCHAR(MAX);
declare @TargetTable table (
URL varchar(128),
Title varchar(128),
Display varchar(128)
);
SET @p = 'url1|title1|display1,url2|title2|display2,url3|title3|display3,';
set nocount on;
with FirstParse as (
select
ItemID,
Item
from
dbo.DelimitedSplit(@p,',')
), SecondParse as (
select
fp.ItemID fpItemID,
sp.ItemID spItemID,
sp.Item
from
FirstParse fp
cross apply dbo.DelimitedSplit(fp.Item,'|') sp
), ParsedData as (
select
max(case spItemID when 1 then Item else '' end) as URL,
max(case spItemID when 2 then Item else '' end) as Title,
max(case spItemID when 3 then Item else '' end) as Display
from
SecondParse
group by
fpItemID
)
insert into @TargetTable(URL,Title,Display)
select
URL,
Title,
Display
from
ParsedData
;
select * from @TargetTable;
set nocount off;
March 31, 2011 at 3:48 am
Hi Lynn,
Thanks for the reply. I've used your code to create a SQL statement that calls my stored procedure to insert the rows. I'm using an external procedure as all the business logic is stored within it and I don't want to reproduce it all. My code is below:
select
@sql = COALESCE(@sql,N'') + N'EXEC dbo.sp_Test @url=' + URL + ',@title=' + title + ',@display=' + Display + ';'
from
ParsedData
;
IF NOT @SQL IS NULL
EXECUTE sp_executesql @statement = @sql
Thanks again!
Kev.
March 31, 2011 at 3:57 am
Glad I was able to help.
March 31, 2011 at 8:25 am
ColdCoffee (3/31/2011)
You can do it with a WHILE...
Alright... who are you and what have you done with the real ColdCoffee?:-P
--Jeff Moden
March 31, 2011 at 8:28 am
kp81 (3/31/2011)
Hi Guys,I've got a situation whereby I have a CSV variable passed to a stored procedure, however that CSV string also contains pipe (|) delimited values to denote individual records. Let me explain:
DECLARE @p VARCHAR(MAX)
SET @p = 'url1|title1|display1,url2|title2|display2,url3|title3|display3'
NOTE: the "probable" maximum number of separate items will be about 100.
My issue is that for each comma-separated "bit", I then need to split it out again into 3 separate pieces; URL, Title and Display in order to execute another stored procedure that inserts it into database.
I've read a lot about WHILE loops and CURSORS and I'd rather not use them, but I can't see how not to when trying to separate out the string and use the individual items when calling a separate procedure.
As always, many thanks for your replies!!
Cheers,
Kev.
I see that Lynn has already sussed this problem... For future reference, check out the following article on how to pass "1, 2, or 3 Dimensional 'Arrays'".
http://qa.sqlservercentral.com/articles/T-SQL/63003/
--Jeff Moden
March 31, 2011 at 12:22 pm
Jeff Moden (3/31/2011)
ColdCoffee (3/31/2011)
You can do it with a WHILE...Alright... who are you and what have you done with the real ColdCoffee?:-P
😛 ah Jeff, i knew it right away that the word WHILE will be thorns for you :w00t: But the OP wanted to execute a SP for each of rows that come out of the DelimitedSplit function. I had no clue, so i suggested WHILE/Dynamic SQL for that..:-)
hmmm... but even in the worst of my dreams, i wouldnt think of WHILE/CURSORs , and thats a promise 😎
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply