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
>>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
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 />