Simple logic - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple logic

Author

Topic

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-15 : 04:16:56

I have record
declare @a varchar(max)
set @a='Hi,Hello,Think'

I need output
Hi,
Hell,
Think

Raghu' S

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-15 : 04:31:16

[code]CREATE FUNCTION dbo.fnParseList
(
@Delimiter CHAR,
@Text varchar(max)
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS

BEGIN
DECLARE @NextPos INT,
@LastPos INT

SELECT @NextPos = CHARINDEX(@Delimiter, @Text, 1),
@LastPos = 0

WHILE @NextPos > 0
BEGIN
INSERT @Result
(
Data
)
SELECT SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)

SELECT @LastPos = @NextPos,
@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
END

IF @NextPos <= @LastPos
INSERT @Result
(
Data
)
SELECT SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos)

RETURN
END
go

declare @a varchar(max)
set @a='Hi,Hello,Think'
select Data from dbo.fnParseList(',',@a)[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.