October 19, 2009 at 7:25 am
...i could use a cursor, but I'm missing something...
except for the varchar (max), this question applies to pretty much all versions that use a function: 2000/2005/2008;
I've got the 24M mthesaur.txt from a thesaurus file I got from the Gutenburg Project that i figured i'd fiddle with for no particular reason other than to build skills.
the file format is a single comma delimited list per line, with the keyword being the first element, and any synonyms following it.
the bulk insert, populating the table, I'm just fine with.
what i was trying to do was to get a single resultset of joining the table against the split of the results.
I have a variety of split functions, but they all seem to be geared towards a static string. i need to join the results of the split back to the original table it came from.
i know i can use a cursor to go thru each row in the table, but i hate pork chops...there's gotta be a way that i'm not seeing.
here's what i thought was what i wanted:
--this is erroring out, not getting what i was expecting
select myAlias.* ,Thesaurus.definition
from [Thesaurus]
cross join dbo.[fn_split]([definition],',') myAlias
basically, the desired results is each element for the split as one field, and the original long comma delimited string in another column.
here's the data setup:
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
GO
alter function [dbo].[fn_split](
@STR varchar(max),
@splitter char(1)
)
returns @returnTable table (idx int primary key identity, item varchar(max))
as
begin
declare @splitterIndex int
select @STR = @STR + @splitter
SELECT @STR = @splitter + @STR + @splitter
INSERT @returnTable
SELECT SUBSTRING(@str,N+1,CHARINDEX(@splitter,@str,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@str)
AND SUBSTRING(@str,N,1) = @splitter
ORDER BY N
return
end
GO
create table [Thesaurus](
TheoID int identity(1,1) primary key,
keyword varchar(100),
definition varchar(max) )
INSERT INTO [Thesaurus](definition)
SELECT 'a cappella,abbandono,accrescendo,affettuoso,agilmente,agitato,amabile,amoroso,appassionatamente,appassionato,brillante,capriccioso,con affetto,con agilita,con agitazione,con amore,crescendo,decrescendo,diminuendo,dolce,forte,fortissimo,lamentabile,leggiero,morendo,parlando,pianissimo,piano,pizzicato,scherzando,scherzoso,sordo,sotto voce,spiccato,staccato,stretto,tremolando,tremoloso,trillando'
UNION ALL
SELECT 'a la mode,advanced,avant-garde,chic,contemporary,dashing,exclusive,far out,fashionable,fashionably,forward-looking,in,in the mode,mod,modern,modernistic,modernized,modish,modishly,newfashioned,now,present-day,present-time,progressive,soigne,soignee,streamlined,stylish,stylishly,tony,trendy,twentieth-century,ultra-ultra,ultramodern,up-to-date,up-to-datish,up-to-the-minute,vogue,voguish,way out'
UNION ALL
SELECT 'a priori,a fortiori,a posteriori,analytic,back,backward,categorical,conditional,deducible,deductive,derivable,dialectic,discursive,dogmatic,early,enthymematic,epagogic,ex post facto,hypothetical,inductive,inferential,into the past,maieutic,reasoned,retroactive,retrospective,soritical,syllogistic,synthetic'
UNION ALL
SELECT 'A-bomb,H-bomb,atomic bomb,atomic warhead,clean bomb,cobalt bomb,dirty bomb,fusion bomb,hell bomb,hydrogen bomb,limited nuclear weapons,nuclear artillery,nuclear explosive,nuclear warhead,plutonium bomb,superbomb,tactical nuclear weapons,thermonuclear bomb,thermonuclear warhead'
--pulls out the keyword for the thesaurus
select LEFT([definition],CHARINDEX(',',[definition])-1)AsTheKeyWord,[definition] As TheDefinition from [Thesaurus]
--gets the split for a given string
select * from dbo.fn_split('A-bomb,H-bomb,atomic bomb,atomic warhead,clean bomb',',')
how do i get the results of the split of a column in the table joined to the original table?
Lowell
October 19, 2009 at 7:37 am
Almost there,
you need cross apply , not cross join
select myAlias.* ,Thesaurus.definition
from Thesaurus
cross apply dbo.[fn_split](Thesaurus.definition,',') myAlias
October 19, 2009 at 7:58 am
ahh;
thanks Dave; That got me jump started so i could make a 1-to-many table that i was fishing for.
Thank you again!
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply