December 1, 2008 at 1:47 am
Hello!
I have this kind of data (about 40 000 rows)
id; keywords
1;horse, red, farm
2;cat, white, farm
3;dog
4;dog, farmer, farm, evening
How do I sererate those keywords into rows? in T-sql?
relults would be something like this
1;horse
2;red
3;farm
4;cat
5;white
6;farm
7;dog
8;dog
9;farmer
10;famr
11;evening
My coal is to make group by clause from all keywords on my keyword colum.
December 1, 2008 at 2:14 am
I have a function which I use to split the address on a few databases (It is separated by a carriage return, so just amend the code to have a "," delimiter instead of a "char(10)"
:
--Function for splitting any field by delimiter "char(10)"
--**TO CALL FROM QA:
--SELECT dbo.nth_substring(address,1), dbo.nth_substring(address,2), dbo.nth_substring(address,3) etc
--FROM XXXtable**
CREATE function nth_substring(@string varchar(255), @n int)
returns varchar(255)
as
begin
declare @start int, @end int, @section int
--set starting positions
set @start = 1
set @section = 1
while (@start > 0) and (@section<@n)
begin
--set start position to be the first occurence of a character return
--and to only run the rest of the query provided there is a CR in place
set @start=charindex(char(10),@string,@start)
if (@start > 0)
--when there is a CR present move the position of the variables to look at the next occurence of a CR
begin
set @section=@section+1
set @start=@start+1
end
end
--While character return is not last character, run charindex to find next position
--set variable @end to be this value
if (@section=@n)
begin
set @end=charindex(char(10),@string,@start)
if (@end=0)
set @end=len(@string)+1
--return substring (text) value of characters with variables setting position of section
return substring(@string,@start,@end-@start)
end
--If no further occurence of CR, return NULL
return NULL
end
December 1, 2008 at 4:06 am
T Lehtinen (12/1/2008)
Hello!I have this kind of data (about 40 000 rows)
id; keywords
1;horse, red, farm
2;cat, white, farm
3;dog
4;dog, farmer, farm, evening
How do I sererate those keywords into rows? in T-sql?
relults would be something like this
1;horse
2;red
3;farm
4;cat
5;white
6;farm
7;dog
8;dog
9;farmer
10;famr
11;evening
My coal is to make group by clause from all keywords on my keyword colum.
http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows
Failing to plan is Planning to fail
December 11, 2008 at 9:32 am
Ian Ritchie (12/1/2008)
I have a function which I use to split the address on a few databases (It is separated by a carriage return, so just amend the code to have a "," delimiter instead of a "char(10)":
--Function for splitting any field by delimiter "char(10)"
--**TO CALL FROM QA:
--SELECT dbo.nth_substring(address,1), dbo.nth_substring(address,2), dbo.nth_substring(address,3) etc
--FROM XXXtable**
CREATE function nth_substring(@string varchar(255), @n int)
returns varchar(255)
as
begin
declare @start int, @end int, @section int
--set starting positions
set @start = 1
set @section = 1
while (@start > 0) and (@section<@n)
begin
--set start position to be the first occurence of a character return
--and to only run the rest of the query provided there is a CR in place
set @start=charindex(char(10),@string,@start)
if (@start > 0)
--when there is a CR present move the position of the variables to look at the next occurence of a CR
begin
set @section=@section+1
set @start=@start+1
end
end
--While character return is not last character, run charindex to find next position
--set variable @end to be this value
if (@section=@n)
begin
set @end=charindex(char(10),@string,@start)
if (@end=0)
set @end=len(@string)+1
--return substring (text) value of characters with variables setting position of section
return substring(@string,@start,@end-@start)
end
--If no further occurence of CR, return NULL
return NULL
end
While loops tend to be really slow for this type of thing... see Madhivanan's link for a better way using a Tally table. If you want a lot more information on how a Tally table actually works to replace loops of this nature, please see the following link...
[font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/font][/url]
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply