iVend Database Database : SplitString User Defined Function

Creation Date

9/18/2015 10:14 AM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@Input

In

VarWChar

1073741823

@Character

In

VarChar

1

@TABLE_RETURN_VALUE

Return Value

Result table returned by table valued function

Empty

0

CREATE FUNCTION [dbo].[SplitString]

(

    @Input NVARCHAR(MAX),

    @Character CHAR(1)

)

RETURNS @Output TABLE (Item NVARCHAR(1000))

AS

BEGIN

    DECLARE @StartIndex INT, @EndIndex INT

    SET @StartIndex = 1

    IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character

    BEGIN

          SET @Input = @Input + @Character

    END

    WHILE CHARINDEX(@Character, @Input) > 0

    BEGIN

          SET @EndIndex = CHARINDEX(@Character, @Input)

          INSERT INTO @Output(Item)

          SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

          SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))

    END

    RETURN

END