SQL Split Function

Favourite

The function to split the string to data table. Useful when you want to pass array like data to storedporcedure.

Code Snippet

CREATE FUNCTION [dbo].[fn_split]
(
      @FullText varchar(Max),
      @Delimiter varchar(5) = ','
)
RETURNS @RtnValue table
(
      Id int identity(1,1),
      Data nvarchar(max)
)
AS
BEGIN
      DECLARE @Cnt INT
      SET @Cnt = 1
 
      WHILE (CHARINDEX(@Delimiter,@FullText)>0)
      BEGIN
            INSERT INTO @RtnValue (data)
            SELECT
                  Data = LTRIM(RTRIM(SUBSTRING(@FullText,1,CHARINDEX(@Delimiter,@FullText)-1)))
 
            SET @FullText = SUBSTRING(@FullText,CHARINDEX(@Delimiter,@FullText)+1,LEN(@FullText))
            SET @Cnt = @Cnt + 1
      END
 
      INSERT INTO @RtnValue (data)
      SELECT Data = LTRIM(RTRIM(@FullText))
 
      RETURN
END

Sample Usage

SELECT * FROM dbo.fn_split('a,b,c',',')

Note

Output will be as follow;

IdData
1a
2b
3c


Categories : Data Development, SQL
Tags : t-sql, split, array


author's photo

Author : Blue Cloud

Just a normal software developer who love to write code. Developing professional software since 1999. After working in the software industry for many years, I've started my own website to share knowledges and experiences.

COMMENTS
blog comments powered by Disqus