SQL Server Performance

Recursive get part of string UDF

Discussion in 'Contribute Your SQL Server Scripts' started by Guus, Jul 15, 2005.

  1. Guus New Member

    I've written this function in VB but converted it to be used in SQL Server.
    This is my first contribution so be gentle.
    Grtz, Guus

    -- Recursive function to get a part of a string
    CREATE FUNCTION dbo.fn_GetPart(@strText varchar(200), @strSep varchar(1), @intPart int )
    RETURNS varchar(200)
    AS
    BEGIN

    DECLARE @intY AS int
    DECLARE @strRetVal AS varchar(200)

    SET @strRetVal = ''

    IF LEN(@strText) = 0
    BEGIN
    RETURN @strRetVal
    END

    SET @intY = CHARINDEX(@strSep, @strText)

    IF @intPart > 1
    BEGIN
    SET @strRetVal = dbo.fn_GetPart(RIGHT(@strText, LEN(@strText) - @intY), @strSep, @intPart -1)
    END
    ELSE
    BEGIN
    IF @intY = 0
    BEGIN
    SET @strRetVal = @strText
    END
    ELSE
    BEGIN
    SET @strRetVal = LEFT(@strText, @intY -1)
    END
    END

    RETURN @strRetVal

    END
  2. Madhivanan Moderator

    >>This is my first contribution so be gentle.

    Good

    SET @strRetVal = dbo.fn_GetPart(RIGHT(@strText, LEN(@strTekst) - @intY), @strSep, @intPart -1)

    should be

    SET @strRetVal = dbo.fn_GetPart(RIGHT(@strText, LEN(@strText) - @intY), @strSep, @intPart -1)

    When creating the function I get the error
    Server: Msg 170, Level 15, State 1, Procedure fn_GetPart, Line 35
    Line 35: Incorrect syntax near '@strRetVal'.

    Can you tell us what your function will do?
    I think substring is enough

    Madhivanan

    Failing to plan is Planning to fail
  3. FrankKalis Moderator

    I've deliberately corrected the typo. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

Share This Page