Post reply

"function"

  • October 17, 2007 at 5:17 am

    #176265

    Let the value be ‘1, 2, 3, 4……100’. How to write a function to split the value and store the numbers in the table variable. can anyone help me to solve this

  • October 17, 2007 at 5:25 am

    #742664

    CREATE FUNCTION dbo.split ( @in NVARCHAR(4000) )

    RETURNS @result TABLE

    ( seqNr INT IDENTITY(1, 1)

    , item NVARCHAR(100)

    )

    AS BEGIN

    DECLARE @i INT

    SET @i = 1

    WHILE ( CHARINDEX(',', @in) > 0 )

    BEGIN

    INSERT INTO @result ( item )

    SELECT LTRIM(RTRIM(SUBSTRING(@in, 1, CHARINDEX(',', @in) - 1)))

    SET @in = SUBSTRING(@in, CHARINDEX(',', @in) + 1, LEN(@in))

    SET @i = @i + 1

    END

    INSERT INTO @result ( item )

    SELECT LTRIM(RTRIM(@in))

    RETURN

    END

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • October 17, 2007 at 5:34 am

    #742670

    Hi,

    There are a few postings and articles for delimiting strings into tables.

    here is one that I saw the last time this someone brough this topic.

    hope it helps

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • October 17, 2007 at 6:58 am

    #742694

    Using a table of numbers as in

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

    DECLARE @STR VARCHAR(100)

    SET @STR='1, 2, 3, 4, 5, 50, 99 ,100'

    SELECT CAST(SUBSTRING(@str,

    Number,

    CHARINDEX(',',

    @STR+',',

    Number)-Number) AS INT) AS Val

    FROM dbo.Numbers

    WHERE Number BETWEEN 1 AND LEN(@str)+1

    AND SUBSTRING(','+@str,Number,1)=','

    This can also be easily be done with a CLR (using a the C# 'Split' function)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply