Menu

Split Function

Retunrs table after breaking stringby given delimiter.

create FUNCTION [dbo].[fn_SplitByComma]
(
         @String VARCHAR(MAX) ,
         @Delimiter CHAR(1)
)
RETURNS @temptable TABLE ( addid VARCHAR(MAX) )
AS
BEGIN
   SET ANSI_NULLS ON
   SET QUOTED_IDENTIFIER ON
   DECLARE @idx INT
   DECLARE @slice VARCHAR(MAX)

   SELECT  @idx = 1
   IF LEN(@String) < 1
       OR @String IS NULL
   RETURN
   WHILE @idx != 0
   BEGIN
        SET @idx = CHARINDEX(@Delimiter, @String)
        IF @idx != 0
              SET @slice = LEFT(@String, @idx - 1)
        ELSE
              SET @slice = @String
        IF ( LEN(@slice) > 0 )
              INSERT  INTO @temptable
           ( addid )
           VALUES  ( @slice )
           SET @String = RIGHT(@String, LEN(@String) - @idx)
       IF LEN(@String) = 0
         BREAK
   END
   RETURN
END

Call this function.

   SELECT  *  FROM  dbo.fn_SplitByComma('1.3.4', '.')

No comments

Leave a Reply


7 + 1 =

Categories