Is there a function for this?! Splitting field up using a delimter

Question

Post reply

Is there a function for this?! Splitting field up using a delimter

learning_sql

Right there with Babe

Points: 727
More actions
October 11, 2012 at 5:26 am

#263178

Hi,
I have a column like this:
"Thing, Thing2, thing14, more things"
I would like to split it up using a view to be able to insert the data into a new table - Is there a function for selecting data and splitting it up using a comma delimiter?
Thanks

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

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

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

Nothing built in.

But have a look here [/url]for a comparison of different ways to achieve this.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

tyson.price SSCrazy Points: 2093 More actions · Answer 2

A little bit down in this post is one example and a link to something someone said is better.

http://www.sqlservercentral.com/Forums/Topic1366387-338-1.aspx

Here is the function in that link:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[DelimitedStringToTable](@String varchar(8000), @Delimiter char(1))

returns @temptable TABLE (items varchar(8000))

as

begin

declare @idx int

declare @slice varchar(8000)

select @idx = 1

if len(@String)<1 or @String is null return

while @idx!= 0

begin

set @idx = charindex(@Delimiter,@String)

if @idx!=0

set @slice = left(@String,@idx - 1)

else

set @slice = @String

if(len(@slice)>0)

insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)

if len(@String) = 0 break

end

return

end

GO

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

tyson.price (10/11/2012)
A little bit down in this post is one example and a link to something someone said is better.
http://www.sqlservercentral.com/Forums/Topic1366387-338-1.aspx
Here is the function in that link:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[DelimitedStringToTable](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
GO

Be careful now.... that's one of the slowest methods there is for splitting delimited strings. Please see the link that Phil (above) posted where he says "here" (that word is the link).

--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)