How to use a T-SQL UDF in a SELECT or UPDATE statement?

Question

Context: SQL Server 2000

I've written a UDF that gives me the text between two other texts, viz

CREATE FUNCTION dbo.StrBetween 
(
    @Text nvarchar(4000),
    @Lhs nvarchar(4000),
    @Rhs nvarchar(4000)
)
RETURNS nvarchar(4000)
AS
BEGIN
    DECLARE @LhsOffset INT;
    DECLARE @RhsOffset INT;
    DECLARE @Result NVARCHAR(4000);

    SET @LhsOffset = CHARINDEX( @Lhs, @Text );
    IF @LhsOffset = 0 
    BEGIN
        RETURN @Text;
    END
    SET @Result = SUBSTRING( @Text, @LhsOffset+1, LEN(@Text)-LEN(@Lhs));
    SET @RhsOffset = CHARINDEX( @Rhs, @Result );
    IF @RhsOffset = 0 
    BEGIN
        RETURN @Result;
    END
    SET @Result = SUBSTRING( @Result, 1, @RhsOffset - 1 );
    RETURN @Result;
END

This works fine in SQL Query Analyser if I have, say,

SELECT dbo.StrBetween('dog','d','g')

However, when I pass a column in as the value of the first argument, I get no response. For example,

SELECT [TEST].[dbo].StrBetween(Referrer,'//', '/') as tst FROM tblTest 

Referrer is declared as an nvarchar field. I'm a newbie when it comes to T-SQL. What obvious thing am I not seeing?

Answer 1

It's not an issue with calling - it's a logic issue, and the fact that your @Rhs value is part of the @Lhs value.

SET @Result = SUBSTRING( @Text, @LhsOffset+1, LEN(@Text)-LEN(@Lhs));

This is removing the first character of your @Lhs string. However, since the second character is /, and that's what your @Rhs match is searching for, it immediately finds it at position 1 and so you get an empty string.

Instead, try:

SET @Result = SUBSTRING( @Text, @LhsOffset+LEN(@Lhs), 4000);

You don't have to be exact with computing a length. If you ask for 4000 characters and the string is only 12 characters long, SUBSTRING will give you back at most 12 characters. So don't bother computing the new length.