July 26, 2011 at 9:21 am
Guys,
I have a column where string stored should be split into multiple lines as select query output based on ' ' delimiter. For example
John VS Smith
to
John
VS
smith
Is there any to insert new line charecter in the select query based on a delimiter.
Any suggestions/inputs would help.
Thanks
July 26, 2011 at 9:25 am
You could use Replace to replace spaces with new lines. Are you familiar with using Replace?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 26, 2011 at 2:12 pm
CREATE FUNCTION dbo.Split(@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
July 26, 2011 at 2:36 pm
Don't use that UDF for this.
There are much better (faster, more reliable) string split functions available.
Also, it doesn't actually do what you asked for. It doesn't add in newline characters, it splits data across rows in a table. Two entirely different things.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 26, 2011 at 9:53 pm
As mentioned by GSquared, if u want to introduce newline in the place of the delimiter you can go for replace function else it would be advisable to look into splitter functions, preferably a set based one..
July 26, 2011 at 10:16 pm
@SQLFRNDZ (7/26/2011)
CREATE FUNCTION dbo.Split(@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
Good golly, NO! 😉 You just don't need the slothfulness of a While loop. Please see the following article...
http://qa.sqlservercentral.com/articles/Tally+Table/72993/
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply