Is there a sql function to split strings?????

Question

Post reply

Is there a sql function to split strings?????

chileu17

Hall of Fame

Points: 3093
More actions
May 19, 2008 at 4:34 pm

#185803

Hi everyone, I have found myself in a hurry of needing to find a sql server function to split words? Like the split function in C#??
For example if I pass to the function a sentence, it should return just the words :hehe: or how could I do this? maybe using a cursor?? or a temp table? Any ideas or suggestions are accepted 😀 Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Search this site for 'split' and you'll find a number of split functions.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 2

i've become very dependent on this:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER function [dbo].[fListToVarchars]( @list varchar(max), @delim varchar(6) )

returns @returnTable table

( item varchar(255) not null, itemSequence smallint not null )

as begin

declare @xml XML

set @xml = char(60)+'item>' + REPLACE(REPLACE(@list,char(60),'<'),@delim,char(60)+'/item>'+char(60)+'item>') + char(60)+'/item>'

insert into @returnTable

SELECT data.item.value('.','varchar(255)'), row_number() over (order by getdate())

FROM @xml.nodes('//item') as data(item)

return

end

;

usage:

select * from dbo.fListToVarchar('mary had a little lamb.',' ');

Jeff Moden SSC Guru Points: 1003916 More actions · Answer 3

I've found XML splits to be just a tad on the slow side. Take a look at the following URL.

http://www.sqlservercentral.com/articles/TSQL/62867/

Also, fairly big article on splits coming out on Wednesday.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)