T-SQL InStrRev

At some point you will need a VB-like InStrRev function in T-SQL. This is a script I found somewhere, for that: 

CREATE FUNCTION dbo.InStrRev (@StringCheckIn VARCHAR(254),@StringCheckFor VARCHAR(254))
     RETURNS INT AS
BEGIN

Declare @StringHere INT
Declare @SpotCount INT
Declare @LookAtChars VARCHAR(254)
Declare @LengthOfStringCheckIn INT
Declare @LengthOfStringCheckFor INT

SET @StringCheckIn = LTrim(RTrim(@StringCheckIn))
SET @StringCheckFor = LTrim(Rtrim(@StringCheckFor))
SET @LengthOfStringCheckIn = Len(@StringCheckIn)
SET @LengthOfStringCheckFor = Len(@StringCheckFor)
SET @SpotCount = @LengthOfStringCheckIn - @LengthOfStringCheckFor

WHILE @SpotCount > '1'
    BEGIN
        SET @LookAtChars = Right(Left(@StringCheckIn,@SpotCount),@LengthOfStringCheckFor)
        IF @LookAtChars = @StringCheckFor
            BEGIN
                SET @StringHere = @SpotCount
                BREAK
            END
        ELSE
            BEGIN
                SET @SpotCount = @SpotCount - 1
                CONTINUE
            END
    END

    RETURN @StringHere
END

To test the function run the following in Query Analyzer:

Declare @string1 VARCHAR(254)
Declare @string2 VARCHAR(254)
Declare @PosString INT

Set @string1 = 'This is the string we are checking for the word are from the right side of the string.'
Set @string2 = 'are'

Set @PosString = dbo.InStrRev(@string1,@string2)

Print @PosString
Advertisements

One Response to T-SQL InStrRev

  1. Dennis Schank says:

    A function is the best idea, but here’s another way to do it without a separate function:

    declare @find varchar(50) set @find = ‘123’
    declare @string varchar(50) set @string = ‘123 abc 123 abc’

    select
    case when charindex(@find,@string) = 0
    then 0
    else (len(@string) + 1) – (charindex(reverse(@find),reverse(@string)) + len(@find) – 1)
    end

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: