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

📱Build WhatsApp Bot using .NET + OpenAI

   In this article, you will learn how to build a real-world WhatsApp chatbot using ASP.NET Core and OpenAI. This bot can automatically repl...