IF OBJECTPROPERTY( OBJECT_ID( 'fn_c_split' ) , 'IsTableFunction' ) = 1
    DROP FUNCTION fn_c_split
GO
CREATE FUNCTION fn_c_split (
    @SourceString varchar( 8000 ) ,
    @Delimiter varchar( 10 ) = ','
)
RETURNS @Values TABLE( Position smallint IDENTITY , Value varchar( 8000 ) )
WITH SCHEMABINDING
AS
BEGIN
    /********************************************************************************/
    /* Created By       : Umachandar Jayachandran (UC)                                  */
    /* Created On       : 22 December 2000                                              */
    /* Description      : This function splits a string based on the specified delimiter*/
    /********************************************************************************/
    /*  Resources  :    https://umachandar.com/resources.htm                         */
    /********************************************************************************/
    DECLARE @Start smallint , @End smallint

    SET @Delimiter = COALESCE( @Delimiter , ',' )
    SET @Start     = LEN( @Delimiter )
    IF LEFT(  @SourceString , LEN( @Delimiter ) ) <> @Delimiter
            SET @SourceString = @Delimiter + @SourceString
    IF RIGHT( @SourceString , LEN( @Delimiter ) ) <> @Delimiter
            SET @SourceString = @SourceString + @Delimiter

    WHILE( 1 = 1 )
    BEGIN
        SET @End           = CHARINDEX( @Delimiter , @SourceString , @Start + 1 )
        IF  @End = 0 BREAK

        INSERT INTO @Values( Value ) VALUES( SUBSTRING( @SourceString , @Start + 1 , @End - @Start - 1 ) )
        SET @SourceString  = STUFF( @SourceString , 1 , @End - 1 , '' )
    END
    RETURN
END
GO

SELECT s.Value FROM dbo.fn_c_split( '1,2,3,4,5,' , ',' ) AS s
ORDER BY s.Position;

SELECT MAX( s.Value ) AS MaxVal FROM dbo.fn_c_split( '123,234,345,456,567,' , ',' ) AS s;

SELECT s.Value FROM dbo.fn_c_split( '||Some||String||Here||' , '||' ) AS s;

SELECT s.Position , s.Value FROM dbo.fn_c_split( N'xxxAAAxxxBBBxxxCCCxxx' , N'xxx' ) AS s;

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