October 14, 2003 at 11:50 pm
Hi,
I have a row which has Data format
Data1|Data2|Data3|Data4|Data5|Data6
I need to split this row into columns such that each Data1, Data2 should appear in a seperate column.
Pls help.
Regards,
Prakash
October 17, 2003 at 8:00 am
This was removed by the editor as SPAM
October 17, 2003 at 8:55 am
Prakash,
Use the following function to split the row
CREATE FUNCTION dbo.fn_Split(@String VARCHAR(8000), @Delimiter CHAR(1))
RETURNS @Results TABLE (Items VARCHAR(8000))
AS
BEGIN
DECLARE @index INT
DECLARE @slice NVARCHAR(4000)
SET @index = 1
WHILE @index !=0
BEGIN
--FIND OUT THE FIRST OCCURENCE OF THE DELIMITER
SET @index = CHARINDEX(@Delimiter,@STRING)
--GET THE FISRT PART OF THE STRING
IF @index !=0
SELECT @slice = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @slice = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Items) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SET @STRING = RIGHT(@STRING,LEN(@STRING) - @index)
-- BREAK OUT
IF LEN(@STRING) = 0 BREAK
END
RETURN
END
usage
SELECT * FROM dbo.fn_Split('Data1|Data2|Data3|Data4|Data5|Data6','|')
Result Set
Data1
Data2
Data3
Data4
Data5
Data6
If you have any questions please let me know.
Thanks,
Sreeni.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply