December 10, 2009 at 5:40 am
I have a field in a table which is a pipe delimited varchar, examples could include:
RT|SS|DAR|AA
RS
RT|SAWA
So, as you can see, the length is variable, the length of the characters within the pipes is variable, you only have pipes if there's more than 1 attribute.
We want to split these out, so that:
ID Types
1 RT|SS|DAR|AA
Becomes
ID Types
1 RT
1 SS
1 DAR
1 AA
Essentially that's it.
I've pulled it into Excel and done text - columns then put the ID between each column I can then cut/paste these 2 column sections below one another, however, I need a way to do all of this within SQL now...
So, any clever ideas anyone?!
December 10, 2009 at 6:31 am
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 10, 2009 at 6:45 am
My try at providing a solution:
First I have this function that I use a lot:
create function [SplitString] (
@String varchar(8000),
@Delimeter char(1) = ',') returns table as
return(
with splitpositions(startposition, endposition) as(
select
startposition = 1,
endposition = charindex(@Delimeter, @String + @Delimeter)
union all
select
startposition = endposition + 1,
endposition = charindex(@Delimeter, @String + @Delimeter, endposition + 1)
from
splitpositions
where
charindex(@Delimeter, @String + @Delimeter, endposition + 1) <> 0)
select
substring(@String, startposition, endposition - startposition) Value
from
splitpositions)
go
And then here is the code to solve the problem:
declare
@test-2 table(
ID int,
Value varchar(max))
insert @test-2
select 1, 'RT|SS|DAR|AA'
union
select 2, 'RT|SAWA'
--You will use the following on your table:
select
ID,
b.Value
from
@test-2 a
cross apply
SplitString(a.Value, '|') b
Hope the format of this post is acceptable to the guys that post to the forums a lot.
I'm still new to this...
December 10, 2009 at 6:58 am
Here is another alternative. The following code is a how I would accomplish this task followed by the code for my function.
create table dbo.TestData (
ID int,
Types varchar(16)
);
insert into dbo.TestData
select 1, 'RT|SS|DAR|AA';
select
td.ID,
ds.Item
from
dbo.TestData td
cross apply dbo.DelimitedSplit(td.Types, '|') ds;
The split function:
USE [SandBox]
GO
/****** Object: UserDefinedFunction [dbo].[DelimitedSplit] Script Date: 12/10/2009 06:52:45 ******/
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
December 10, 2009 at 7:01 am
I've been playing around with this, and come up with this, which seems to work:
DECLARE loop_cursor CURSOR
FOR
SELECT id, type FROM tblA WHERE id < 100
OPEN loop_cursor
DECLARE @id int, @type varchar(100)
FETCH NEXT FROM loop_cursor INTO @id, @type
while @@FETCH_STATUS = 0
begin
insert into #tmpTest
select @id, items from dbo.Split(@type,'|')
FETCH NEXT FROM loop_cursor INTO @id, @type
end
CLOSE loop_cursor
DEALLOCATE loop_cursor
This seems to do it 😀
December 10, 2009 at 7:04 am
Rob-350472 (12/10/2009)
I've been playing around with this, and come up with this, which seems to work:
DECLARE loop_cursor CURSORFOR
SELECT id, type FROM tblA WHERE id < 100
OPEN loop_cursor
DECLARE @id int, @type varchar(100)
FETCH NEXT FROM loop_cursor INTO @id, @type
while @@FETCH_STATUS = 0
begin
insert into #tmpTest
select @id, items from dbo.Split(@type,'|')
FETCH NEXT FROM loop_cursor INTO @id, @type
end
CLOSE loop_cursor
DEALLOCATE loop_cursor
This seems to do it 😀
LOL works for me!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 10, 2009 at 7:07 am
The cursor is not a very elegant solution though...
😛
December 10, 2009 at 7:10 am
I did wonder if the words cursor would lead to criticism! - I'll read through the other replies above and give them a go when I get a chance.
It's only for a one off task too, so not being overly elegant or performance driven is not a huge curse 🙂
December 10, 2009 at 7:13 am
Agreed, the cursor solution, though workable, is not scalable. Of the three alternatives presented here at this time, IMHO, the tally version is actually the most scalable solution. Sorry, but the recurisive version is also not as scalable, it is also RBAR.
December 10, 2009 at 7:16 am
Rob-350472 (12/10/2009)
I did wonder if the words cursor would lead to criticism! - I'll read through the other replies above and give them a go when I get a chance.It's only for a one off task too, so not being overly elegant or performance driven is not a huge curse 🙂
Not the best way to be thinking. You may only need this NOW for a one off task. The possibility exists down the road that you may find a need for string spliting and having a high performing solution my be needed at that time.
December 10, 2009 at 7:52 am
@Lynn, I tried your method, which worked fine on the small sample you gave, however, when I try it on my table I get :
Msg 1014, Level 15, State 1, Line 1
TOP clause contains an invalid value.
Just using this query:
SELECT
id,
ds.item
FROM tblTest A
cross apply dbo.DelimitedSplit(a.type, '|') ds;
Any ideas what would be causing this?
Edit: It looks like it was null types which were causing this to fail, removing those it works a treat 🙂
Thanks 😀
December 10, 2009 at 7:57 am
Rob-350472 (12/10/2009)
@Lynn, I tried your method, which worked fine on the small sample you gave, however, when I try it on my table I get :Msg 1014, Level 15, State 1, Line 1
TOP clause contains an invalid value.
Just using this query:
SELECT
id,
ds.item
FROM tblTest A
cross apply dbo.DelimitedSplit(a.type, '|') ds;
Any ideas what would be causing this?
Edit: It looks like it was null types which were causing this to fail, removing those it works a treat 🙂
Thanks 😀
Check your data. Are there any null values or empty strings?
December 10, 2009 at 7:59 am
Just ran a quick test, you probably have null values as an empty string does not return an error.
December 10, 2009 at 1:29 pm
Here is how I implemented something similar for a comma delimited entity, that inserts the results into a table variable:
declare @theseaccounts as TABLE (lastpull char(8), account varchar(16))
declare @ii int
declare @pos int
set @pos = 1
set @ii = charindex(',',@account,@pos)
if @ii = 0 -- no commas
insert @theseAccounts values (NULL, @account)
else
begin
while @ii > 0
begin
insert @theseAccounts values (NULL, SUBSTRING(@account,@pos,@ii-@pos))
set @pos = @ii+1
set @ii = charindex(',',@account,@pos)
end
insert @theseAccounts values (NULL, SUBSTRING(@account,@pos,LEN(@account)-@pos+1))
end
The probability of survival is inversely proportional to the angle of arrival.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply