Saturday, October 5, 2013

Split value Comma Separated String in to Separate records SQL SERVER


/******  [dbo].[Split] Script  ******/

CREATE FUNCTION [dbo].[Split]
(
@RowData NVARCHAR(MAX),
@Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE
(
ID INT IDENTITY(1,1),
Data NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @Iterator INT
SET @Iterator = 1

DECLARE @FoundIndex INT
SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)

WHILE (@FoundIndex>0)
BEGIN
INSERT INTO @RtnValue (data)
SELECT
Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))

SET @RowData = SUBSTRING(@RowData,
@FoundIndex + DATALENGTH(@Delimeter) / 2,
LEN(@RowData))

SET @Iterator = @Iterator + 1
SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
END

INSERT INTO @RtnValue (Data)
SELECT Data = LTRIM(RTRIM(@RowData))

RETURN
END

***************************************************************
use :

SELECT ID , Data FROM dbo.Split('manish,chetan,bala,nitesh,nital',',')

No comments:

Post a Comment

Opps Part 1 : Abstraction

  Abstraction in C# is a fundamental concept of object-oriented programming (OOP) that allows developers t...