October 28, 2004 at 3:16 pm
aaaaa,bbbb,ccccc,dddd,ffff,gggg,llll,kkkk,nnnnn
How can split the above string to the following individual strings
aaaaa
bbbb
ccccc
dddd
ffff
gggg
llll
kkkk
nnnnn
Thanks
October 28, 2004 at 5:33 pm
Create the following function which accepts a comma delimited, optionally quoted string and returns the separated result as a table:
CREATE function fnSplitString (@s nvarchar(4000)) returns @strings table (value nvarchar(100)) as
begin
declare @temp nvarchar(100), @quote bit
set @temp=''
set @quote=0
while len(@s)<>0
begin
if left(@s,2)=''''''
begin
if @quote=0
insert into @strings values ('')
else
set @temp=@temp+''''
set @S=substring(@s,3,len(@s)-2)
end
if left(@s,1)=''''
begin
if @quote=0
set @quote=1
else
begin
set @quote=0
insert into @strings values (@temp)
set @temp=''
end
set @S=substring(@s,2,len(@s)-1)
end
if left(@s,1)=','
begin
if @quote=0
begin
if len(@temp)<>0
insert into @strings values (@temp)
set @temp=''
end
else
set @temp=@temp+','
set @S=substring(@s,2,len(@s)-1)
end
else if len(@s)<>0
begin
set @temp=@temp+left(@s,1)
set @S=substring(@s,2,len(@s)-1)
end
end
if len(@temp)<>0
insert into @strings values (@temp)
return
end
The following statement...
...will return:
I'm going to be sooo embarassed now if someone comes up with a built-in method of doing this!
October 28, 2004 at 11:34 pm
http://www.sommarskog.se/arrays-in-sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 29, 2004 at 5:22 am
DECLARE @name varchar(500)
DECLARE @len int,@start int,@start1 int,@len_original int
SET @start1=0
SET @name='aaa,bbb,ccc,ddd,eee,fff'
SELECT @len_original=LEN(@name)
SELECT @len=LEN(@name)
SET @start=1
WHILE @len>1
BEGIN
SELECT SUBSTRING(@name, 1, CHARINDEX(',', @name)-1 ) as user_name
SELECT @start=CHARINDEX(',', @name)+1
SELECT @start1=@start1+@start
SELECT @name=SUBSTRING(@name,@start,(@len-@start))
SELECT @len=@len-@start+1
IF @start1=@len_original
BEGIN
SELECT @name AS user_name
BREAK
END
ELSE CONTINUE
END
how can i optimize the above code?
July 25, 2006 at 4:39 pm
ALTER FUNCTION dbo.fnStringToTable(@CommaDelimList varchar(5000), @Delim char(1))
--*********************************
--Example;
--select *
--from dbo.fnStringToTable('one, two, three, four', ',')
--
--Author: Ed Hellyer
--Date: Tuesday July 26th 2006
--
--*********************************
RETURNS @List TABLE (Value varchar(100))
AS
BEGIN
declare @Start int
set @Start = 1
while @Start is not null
begin
insert into @List(Value) select LTrim(RTrim(SubString(@CommaDelimList, @Start, abs(@Start - IsNull(NullIf(CharIndex(@Delim, @CommaDelimList, @Start + 1), 0), Len(@CommaDelimList) + 1)))))
set @Start = NullIf(CharIndex(@Delim, @CommaDelimList, @Start + 1), 0) + 1
end
return
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply