April 2010 Tip of the Month

SQL Server Development:  Take apart delimited strings with ease with a function that is commonly found in Visual Basic.  Pass a delimited string into the fnSplit function and it will output the string as a table that can be queried. 

The function is called by this statement: 

SELECT * FROM dbo.fnSplit(‘SQLRx,Solves,Performance,Problems’,’,’)

It will output a table…give it a try!

CREATE FUNCTION [dbo].[fnSplit] (@String VARCHAR(8000), @Delimiter CHAR(1))       

      RETURNS @temptable TABLE (items VARCHAR(8000))         

      AS         

      BEGIN             

            DECLARE @idx INT              

            DECLARE @slice VARCHAR(8000)  

            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(items) VALUES(@slice)

                  SET @String = RIGHT(@String,LEN(@String) – @idx)                 

                  IF LEN(@String) = 0 BREAK             

            END     

      RETURN         

      END

GO

Both comments and trackbacks are currently closed.
%d bloggers like this: