Hi,
Use the following Logic ....
CREATE FUNCTION [dbo].[fnSplit](
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(val)
SELECT r.value('.','varchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END
SELECT val FROM fnSplit('Micro Soft,SQL Server 2008, R2',',')
IF OBJECT_ID('fnSplit') IS NOT NULL DROP FUNCTION dbo.fnSplit
GO
CREATE FUNCTION dbo.fnSplit(
@InputString VARCHAR(8000),
@Delimiter CHAR(1) = ',')
RETURNS @List TABLE (ColumnValues VARCHAR(8000))
AS
BEGIN
DECLARE @NextString NVARCHAR(40), @Pos INT
SET @InputString = @InputString + @Delimiter
SET @Pos = CHARINDEX(@Delimiter,@InputString)
WHILE (@pos <> 0)
BEGIN
SET @NextString = SUBSTRING(@InputString,1,@Pos - 1)
INSERT INTO @List(ColumnValues) SELECT @NextString
SET @InputString = SUBSTRING(@InputString,@pos+1,len(@InputString))
SET @pos = CHARINDEX(@Delimiter,@InputString)
END
RETURN
END
GO
SELECT ColumnValues FROM fnSplit('Micro Soft,SQL Server 2008, R2',',')
Regards,
GVPrabu