splitcsv

Question

Post reply

splitcsv

gloriasyc

SSC Rookie

Points: 39
More actions
January 15, 2010 at 8:42 pm

#137499

ALTER FUNCTION [dbo].[SplitCSV](
@csv NVARCHAR(4000),
@delm CHAR(1))
RETURNS @Result TABLE
( Element NVARCHAR(4000))
AS
BEGIN
DECLARE @p INT, @lastP INT, @quit CHAR(1)
SET @p = 1
SET @lastP = 0
SET @quit = 'N'
IF (LEN(@csv) = 0)
BEGIN
SET @quit = 'Y'
END
IF (@csv is null )
BEGIN
SET @quit = 'Y'
END
WHILE @quit = 'N'
BEGIN
SET @p = CHARINDEX(@delm, @csv, @lastP + 1)
IF (@p = 0)
BEGIN
INSERT INTO @Result VALUES (SUBSTRING(@csv, @lastP + 1, LEN(@csv) -
@lastP))
SET @quit = 'Y'
END
ELSE
INSERT INTO @Result VALUES (SUBSTRING(@csv, @lastP + 1, @p - @lastP - 1))
SET @lastP = @p -1
END
RETURN
END
How can I change the above program to dispaly as follow when I type the following select statement
select * from SplitCSV('ab,cd,ef',',')
Result:
ab
ab,cd
ab,cd,ef

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Would you share with us why you want to do this unusual thing first?

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

arun.sas SSChampion Points: 11831 More actions · Answer 2

Jeff Moden (1/15/2010)
Would you share with us why you want to do this unusual thing first?

Hi Jeff,

The OP needs to set the posted function (may created for another reason) to their requirement.

Plain solution with tally table (article)

Declare @Parameter varchar(max),

@delm char(1)

set @Parameter = 'ab,bc,cd'

set @delm = ','

set @Parameter = @delm+@Parameter+@delm

SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)

FROM dbo.Tally /*Refer Jeff article to create this number table*/

WHERE N < LEN(@Parameter)

AND SUBSTRING(@Parameter,N,1) = @delm

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

arun.sas (1/15/2010)
Jeff Moden (1/15/2010)
Would you share with us why you want to do this unusual thing first?
Hi Jeff,
The OP needs to set the posted function (may created for another reason) to their requirement.
Plain solution with tally table (article)
Declare @Parameter varchar(max),
@delm char(1)
set @Parameter = 'ab,bc,cd'
set @delm = ','
set @Parameter = @delm+@Parameter+@delm
SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)
FROM dbo.Tally /*Refer Jeff article to create this number table*/
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = @delm

Heh... thanks Arun and, yeah, I know how to do it with a Tally table 😛 but I really wanted to know why the OP wanted to do this. Once most OPs have their answer, they never come back to share the real reason. Knowing the real reason for such a requirement is as important to me as the correct answer is to the OP. Be patient, my friend. 😉

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