How To split string in Sql Server?

Split String Function

Sometime we need to split string in sql  server like Comma sparated(,) or colon(:) or dot(.) or (#)

For examples:


declare @Pname Nvarchar(MAX)
SET @Pname ='XZY,TTT,YYY,UUU'

select *
    FROM [dbo].[SplitString] (@Pname,',')

You can Use Following Function to Split Sting in Sql Sever


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[SplitString](@text varchar(8000), @delimiter varchar(1) = ',')
-- This function splits a string of CSV values and creates a table variable with the values.
-- Returns the table variable that it creates
RETURNS @Strings TABLE
(
    position int IDENTITY PRIMARY KEY,
    member_id varchar(8000)
)

AS

BEGIN
    Declare @index int  
    Set @index = -1  
 
    WHILE (LEN(@text) > 0)  
       BEGIN
        SET @index = CHARINDEX(@delimiter , @text)
        IF (@index = 0) AND (LEN(@text) > 0)
               BEGIN
               INSERT INTO @Strings VALUES (@text)
            BREAK
           END
 
        IF (@index > 1)
                 BEGIN
            INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
            SET @text = RIGHT(@text, (LEN(@text) - @index))
           END
            ELSE  
            SET @text = RIGHT(@text, (LEN(@text) - @index))
 
       END  
    RETURN
END