November 3, 2008 at 10:16 am
I have a column in a table the contains delimited strings. I would like to create a view that would have the parsed values in thier own column. I currently have a script to do one at a time but having trouble changing it to be set based. Below is current script:
DECLARE @Delimiter Char(1)
DECLARE @StringToParse VarChar(MAX)
DECLARE @CharactersLeftToParse INT
DECLARE @LoopCounter INT
DECLARE @ParsedString VarChar(100)
DECLARE @OrigValue Varchar(50)
SET @Delimiter = ','
SET @StringToParse = 'how,can,this,be,done'
SET @OrigValue = @StringToParse
SET @CharactersLeftToParse =Len(@StringToParse)
SET @LoopCounter=1
CREATE TABLE #ParsedValuesTable
(
id int identity(1,1),
OrigValue varchar(50),
ParsedValue VarChar(50)
)
IF (CHARINDEX(@Delimiter, @StringToParse,1) = 0)
PRINT @StringToParse
WHILE (CHARINDEX(@Delimiter, @StringToParse,1) <> 0)
BEGIN
IF @LoopCounter = 1
SET @ParsedString = SUBSTRING(@StringToParse, @LoopCounter, CHARINDEX(@Delimiter,@StringToParse,1) - 1)
INSERT INTO #ParsedValuesTable (OrigValue,ParsedValue) VALUES (@OrigValue,@ParsedString)
SET @StringToParse = SUBSTRING(@StringToParse, Len(@ParsedString) + 2, Len(@StringToParse))
SET @CharactersLeftToParse = @CharactersLeftToParse - 1
IF (CHARINDEX(@Delimiter, @StringToParse,1) = 0)
INSERT INTO #ParsedValuesTable (OrigValue,ParsedValue) VALUES (@OrigValue,@StringToParse)
END
SELECT * from #ParsedValuesTable
pivot
(
max(ParsedValue)
for [id] in ([1],[2],[3],[4],[5])
)as p
DROP TABLE #ParsedValuesTable
November 3, 2008 at 10:52 am
Do a search on this site for Tally table. I think this will help you solve your problem.
November 3, 2008 at 3:32 pm
Here's an example of parsing using a tally table, but the article is really worth reading. This example creates the tally table on the fly from CTE's, but you can also just create one of a million rows or so in your database.
------------------------------------------------------------------------------------------------------------------
DECLARE @Delimiter Char(1)
DECLARE @StringToParse VarChar(MAX)
declare @timer datetime
SET @Delimiter = ','
SET @StringToParse = 'how,can,this,be,done,really,really,quickly'
select @stringToParse
set @timer = getdate()
select @stringToParse = @delimiter+@stringToParse+@delimiter
;WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows "L0 as A, L0 as B" is just shorthand way to code a cross join
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
Tally AS (SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4),
Array AS
(select Row_Number() over (order by N) as E,substring(@stringToParse,N+1,charindex(@delimiter,@stringToParse,N+1)-(N+1)) as element
from tally
where substring(@stringToParse,N,1) = @delimiter
and N < len(@stringToParse)
)
select max(case when e = 1 then element else null end) as Col1
,max(case when e = 2 then element else null end) as Col2
,max(case when e = 3 then element else null end) as Col3
,max(case when e = 4 then element else null end) as Col4
,max(case when e = 5 then element else null end) as Col5
,max(case when e = 6 then element else null end) as Col6
,max(case when e = 7 then element else null end) as Col7
,max(case when e = 8 then element else null end) as Col8
,max(case when e = 9 then element else null end) as Col9
,max(case when e = 10 then element else null end) as Col10
from array
select datediff(ms,@timer,getdate())
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 3, 2008 at 7:22 pm
Robert,
Take a look at the following article. It explains what a Tally table is, how it works, has a couple of examples including how to split a whole table's worth of CSV's, and a good method to build either a permanent Tally table, or an "on the fly" CTE that runs very fast.
http://qa.sqlservercentral.com/articles/TSQL/62867/
Look for the section titled [font="Arial Black"]"One Final "Split" Trick with the Tally Table"[/font] for the solution to your problem.
Please feel free to post back if you have any implementation problems with this.
--Jeff Moden
November 4, 2008 at 8:15 am
The tally table is definately something I can use, thank to all who pointed it out. But I am now having trouble now getting all the parsed values into the same row.
here is what I currently have. this outputs one row for each parsed value but i would need all values in the same row.
ie how,to,do,this
would result into
________________________________
|how,to,do,this | how | to | do | this |
________________________________
create table #parsed
(csv varchar(50))
insert into #parsed
select 'how,to,do,this'
union
select 'how,is,this,done'
union
select 'i,dont,know'
union
select 'but,i,want,to,know'
--select * from #parsed;
WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows "L0 as A, L0 as B" is just shorthand way to code a cross join
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
Tally2 AS (SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4),
ranknum as(
select dense_rank() over (ORDER BY csv) AS [rank],csv from #parsed),
total as
(SELECT rnk.[rank],p.csv, SUBSTRING(','+p.csv+',',N+1,CHARINDEX(',',','+p.csv+',',N+1)-N-1) AS Value
FROM Tally2 t
CROSS JOIN #parsed p
inner join ranknum rnk
on rnk.[csv] = p.csv
WHERE N < LEN(','+p.csv+',')
AND SUBSTRING(','+p.csv+',',N,1) = ',')
select [rank]as [rank],csv,[Value] as [value] from total
order by [rank]
drop table #parsed
November 4, 2008 at 9:26 am
I seem to have gotten it to work (kind of). i have created a stored proc which outputs what i need but i would like it searchable so I added parameters to it. With the parameters in the stores proc no results are returned but if i hard code the parameters in the where clause I get correct results. Anybody help with this or suggest a different way of doing this.
create proc csvparsed
@col varchar(5) ,
@search varchar(50)
as
declare @parsed table
(csv varchar(50) not null);
insert into @parsed
select 'how,to,do,this'
union
select 'how,is,this,done'
union
select 'i,dont,know'
union
select 'but,i,want,to,know';
--select * from #parsed;
declare @total table
([rank2] int,
[rank] int,
[csv] varchar(50),
[value] varchar(50));
WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows "L0 as A, L0 as B" is just shorthand way to code a cross join
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
Tally2 AS (SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4),
ranknum as(
select rank() over (order by csv) AS [rank],csv from @parsed),
total as
(SELECT rnk.[rank],p.csv, SUBSTRING(','+p.csv+',',N+1,CHARINDEX(',',','+p.csv+',',N+1)-N-1) AS Value
FROM Tally2 t
CROSS JOIN @parsed p
inner join ranknum rnk
on rnk.[csv] = p.csv
WHERE N < LEN(','+p.csv+',')
AND SUBSTRING(','+p.csv+',',N,1) = ',')
insert into @total
select row_number() over (partition by [rank] order by csv) AS [rank2],[rank]as [rank],csv,[Value] as [value] from total;
select csv,[1],[2],[3],[4],[5] from @total
pivot
(
max([Value])
for [rank2] in ([1],[2],[3],[4],[5])
)as p
where @col = @search;
exec csvparsed @col='[1]', @search = 'how'
November 4, 2008 at 3:43 pm
Look at your where clause: @col = @search !!!
Using your input parms you are asking for rows where '[1]'='how'.
Usually you have to do dynamic sql to pass a parm for what column to search.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 4, 2008 at 4:24 pm
Robert klimes (11/4/2008)
The tally table is definately something I can use, thank to all who pointed it out. But I am now having trouble now getting all the parsed values into the same row.here is what I currently have. this outputs one row for each parsed value but i would need all values in the same row.
ie how,to,do,this
would result into
________________________________
|how,to,do,this | how | to | do | this |
I have to ask two questions...
Why do you want to do this? In other words, what is the actual requirement? I ask because sometimes there's a better way available.
And, what would the column names for this be especially since you're asking for a potentially variable number of columns?
--Jeff Moden
November 4, 2008 at 5:48 pm
(Conceding in advance that if you answer Jeff's question he will know a better way...)
This approach can handle your search, but comes with two caveats: (1) It involves creation of a user defined function, and (2) the "search" will be a table scan.
-------------------------------------------------------------------------------------------------------------
-- Here's the user defined function.
-------------------------------------------------------------------------------------------------------------
CREATE FUNCTION dbo.ufnParseAndSearch
(
@input varchar(1000)
,@delimiter char(1)
,@searchCol int
,@searchString varchar(100)
)
RETURNS @returnTbl TABLE
(
[1] varchar(100)
,[2] varchar(100)
,[3] varchar(100)
,[4] varchar(100)
,[5] varchar(100)
,[6] varchar(100)
,[7] varchar(100)
,[8] varchar(100)
,[9] varchar(100)
,[10] varchar(100)
)
AS
BEGIN
declare @workTbl table (E int primary key, element varchar(100))
set @input = @delimiter+@input+@delimiter
;WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1)
,L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B)
,L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B)
,L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B)
,L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B)
,Tally AS (SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4)
,Array AS
(select Row_Number() over (order by N) as E,substring(@input,N+1,charindex(@delimiter,@input,N+1)-(N+1)) as element
from tally
where substring(@input,N,1) = @delimiter
and N < len(@input)
)
insert into @workTbl
select E,element from Array
if exists (select 1 from @workTbl where E = @searchCol and element = @searchString)
begin
insert into @returnTbl
select sum(case when e = 1 then element else null end) as [1]
,sum(case when e = 2 then element else null end) as [2]
,sum(case when e = 3 then element else null end) as [3]
,sum(case when e = 4 then element else null end) as [4]
,sum(case when e = 5 then element else null end) as [5]
,sum(case when e = 6 then element else null end) as [6]
,sum(case when e = 7 then element else null end) as [7]
,sum(case when e = 8 then element else null end) as [8]
,sum(case when e = 9 then element else null end) as [9]
,sum(case when e = 10 then element else null end) as [10]
from @workTbl
end
RETURN
END
GO
/* test
select * from dbo.ufnParseAndSearch ('how,about,this,action',',',1,'howl')
*/
---------------------------------------------------------------------------------------
-- and here is how you use it in your example
---------------------------------------------------------------------------------------
DECLARE @input TABLE (StringToParse VarChar(100))
DECLARE @Delimiter Char(1)
declare @col int
declare @search varchar(100)
declare @timer datetime
SET @Delimiter = ','
set @col = 1
set @search = 'how'
insert into @input
select 'how,can,this,be,done' union all
select 'how,is,it,possible' union all
select 'it,may,not,be' union all
select 'but,then,again' union all
select 'how,can,this,be,done,really,really,quickly'
set @timer = getdate()
select *
from @input
cross apply dbo.ufnParseAndSearch(StringToParse,',',@col,@search)
select datediff(ms,@timer,getdate())
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 6, 2008 at 8:38 am
Why do you want to do this? In other words, what is the actual requirement? I ask because sometimes there's a better way available.
And, what would the column names for this be especially since you're asking for a potentially variable number of columns?
The results of this will be joined to other tables for use in reports. And after further investigation the number of parsed coulmns will be static. In light of this I have written a quick view that satisfies my needs but it is quite unreadable and what was sudgested and I was trying to accomplish with the tally table may be a better way to go.
this is the view I have created
create table parse
(stringToParse varchar(50));
insert into parse
select 'this,is,the,parse,view'
union all
select 'hard,to,read,the,sql'
union all
select 'open,to,a,better,way';
alter view parse_view (
stringToParse,col1,col2,col3,col4,col5)
as
select stringToParse,
substring(stringToParse,1,charindex(',',stringToParse)-1),
substring(stringToParse,charindex(',',stringToParse)+1,charindex(',',stringToParse,charindex(',',stringToParse,charindex(',',stringToParse)+1))-charindex(',',stringToParse)-1),
substring(stringToParse,charindex(',',stringToParse,charindex(',',stringToParse)+1)+1,charindex(',',stringToParse,charindex(',',stringToParse,charindex(',',stringToParse)+1)+1)-charindex(',',stringToParse,charindex(',',stringToParse)+1)-1),
substring(stringToParse,charindex(',',stringToParse,charindex(',',stringToParse,charindex(',',stringToParse)+1)+1)+1,charindex(',',stringToParse,charindex(',',stringToParse,charindex(',',stringToParse,charindex(',',stringToParse)+1)+1)+1)-charindex(',',stringToParse,charindex(',',stringToParse,charindex(',',stringToParse)+1)+1)-1),
substring(stringToParse,charindex(',',stringToParse,charindex(',',stringToParse,charindex(',',stringToParse,charindex(',',stringToParse)+1)+1)+1)+1,len(stringToParse))
from dbo.parse;
select * from parse_view
November 7, 2008 at 6:55 pm
Robert klimes (11/6/2008)
And after further investigation the number of parsed coulmns will be static.
So... how many columns?
--Jeff Moden
November 9, 2008 at 1:02 pm
there will be 5 columns. The string to be parsed takes the form of
123456*123456*ABCDE*9876*1
columns 3 (ABCDE) and 4 (9876) are variable length of unknown size, column 1 and 2 are variable upto a length of 6 numbers, and column 5 will always be the value 1,2,or 3.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply