IF OBJECTPROPERTY( OBJECT_ID( 'fn_split' ) , 'IsTableFunction' ) = 1
        DROP FUNCTION fn_split
GO
CREATE FUNCTION fn_split (
    @SourceString sql_variant,
    @Delimiter nvarchar(10) = N','
)
RETURNS @Values TABLE( Position smallint IDENTITY, cValue varchar( 8000 ) , ncValue nvarchar( 4000 ) )
AS
BEGIN
    /********************************************************************************/
    /* Created By       : Umachandar Jayachandran (UC)                                  */
    /* Created On       : 22 December 2000                                              */
    /* Description      : This function splits an Ascii or Unicode string based on the  */
    /*                specified delimiter.                                          */
    /********************************************************************************/
    /*  Resources  :    https://umachandar.com/resources.htm                         */
    /********************************************************************************/
    DECLARE @NormalString    varchar( 8000 ) , @NationalString    nvarchar( 4000 ) ,
            @NormalDelimiter varchar( 10 )   , @NationalDelimiter nvarchar( 10 )   ,
            @IsNationalChar  bit             , @Position          int              ,
            @NormalValue     varchar( 8000 ) , @NationalValue     nvarchar( 4000 )
    SET @Delimiter      = COALESCE( @Delimiter, N',' )
    SET @IsNationalChar = CASE
                            WHEN SQL_VARIANT_PROPERTY( @SourceString , 'BaseType' ) IN ( 'char' , 'varchar' )
                            THEN 0
                            WHEN SQL_VARIANT_PROPERTY( @SourceString , 'BaseType' ) IN ( 'nchar' , 'nvarchar' )
                            THEN 1
                          END
    IF @IsNationalChar IS NULL RETURN
    IF @IsNationalChar = 0
    BEGIN
            SET @NormalDelimiter = @Delimiter
            SET @NormalString    = CAST( @SourceString AS varchar(8000) )

            IF LEFT( @NormalString , LEN( @NormalDelimiter ) ) = @NormalDelimiter
                    SET @NormalString = SUBSTRING( @NormalString, LEN( @NormalDelimiter ) + 1, 8000 )
            IF RIGHT( @NormalString , LEN( @NormalDelimiter ) ) <> @NormalDelimiter
                    SET @NormalString = @NormalString + @NormalDelimiter
            WHILE( 1 = 1 )
            BEGIN
                    SET @Position     = CHARINDEX( @NormalDelimiter , @NormalString ) - 1
                    IF @Position <= 0 BREAK
                    SET @NormalValue  = LEFT( @NormalString , @Position )
                    SET @NormalString = STUFF( @NormalString , 1 , @Position + LEN( @NormalDelimiter ), '' )
                    INSERT INTO @Values ( cValue ) VALUES( @NormalValue )
            END
    END
    ELSE IF @IsNationalChar = 1
    BEGIN
            SET @NationalDelimiter = @Delimiter
            SET @NationalString    = CAST( @SourceString AS varchar(8000) )

            IF LEFT( @NationalString , LEN( @NationalDelimiter ) ) = @NationalDelimiter
                    SET @NationalString = SUBSTRING( @NationalString, LEN( @NationalDelimiter ) + 1, 4000 )
            IF RIGHT( @NationalString , LEN( @NationalDelimiter ) ) <> @NationalDelimiter
                    SET @NationalString = @NationalString + @NationalDelimiter
            WHILE( 1 = 1 )
            BEGIN
                    SET @Position     = CHARINDEX( @NationalDelimiter , @NationalString ) - 1
                    IF @Position <= 0 BREAK
                    SET @NationalValue  = LEFT( @NationalString , @Position )
                    SET @NationalString = STUFF( @NationalString , 1 , @Position + LEN( @NationalDelimiter ), '' )
                    INSERT INTO @Values ( ncValue ) VALUES( @NationalValue )
            END
    END
    RETURN
END
GO

SELECT s.Position , s.cValue
  FROM dbo.fn_split( '1,2,3,4,5,' , default ) AS s
 ORDER BY s.Position
/*
cValue                                                                                                                                                                                                                                                           
------
1
2
3
4
5
*/

SELECT s.cValue FROM dbo.fn_split( '||Some||String||Here' , '||' ) AS s
/*
cValue                                                                                                                                                                                                                                                           
------
Some
String
Here
*/

SELECT s.ncValue FROM dbo.fn_split( N'xxxAAAxxxBBBxxxCCCxxx' , N'xxx' ) AS s
/*
ncValue                                                                                                                                                                                                                                                          
-------
AAA
BBB
CCC
*/

This page was last updated on May 01, 2006 04:28 PM.