Tuesday, March 5, 2013

function to use comma seprated values in to a table so that it can be use in IN statement in sql



function to use comma seprated values in to a table so that it can be use in IN statement in sql

ALTER FUNCTION [dbo].[fnNTextToIntTable] (@Data nvarchar(4000))
RETURNS
    @IntTable TABLE ([Value] NVARCHAR(500) NULL)
AS
BEGIN
    SET @Data=Replace(@Data,'$',',')
    DECLARE @Ptr int, @Length int, @v nchar, @vv nvarchar(10)
    SELECT @Length = (DATALENGTH(@Data) / 2) + 1, @Ptr = 1
    WHILE (@Ptr < @Length)
    BEGIN
        SET @v = SUBSTRING(@Data, @Ptr, 1)
        IF @v = ','
        BEGIN
            INSERT INTO @IntTable (Value) VALUES (CAST(RTRIM(LTRIM(@vv)) AS NVARCHAR(500)))
            SET @vv = NULL
        END
        ELSE
        BEGIN
            SET @vv = ISNULL(RTRIM(LTRIM(@vv)), '') + @v
        END
        SET @Ptr = @Ptr + 1
    END
    -- If the last number was not followed by a comma, add it to the result set
    IF @vv IS NOT NULL
        INSERT INTO @IntTable (Value) VALUES (CAST(RTRIM(LTRIM(@vv)) AS NVARCHAR(500)))
    RETURN
END

No comments:

Post a Comment