Find LastIndex in SQL Server

CREATE FUNCTION dbo.LASTINDEX(@STRING VARCHAR(8000), @CHAR CHAR)
RETURNS INT
AS
BEGIN
RETURN LEN(@STRING) – CHARINDEX(@CHAR, REVERSE(@STRING), 1 + 1)
END

About these ads

2 thoughts on “Find LastIndex in SQL Server

  1. Hi Sujit

    That code does`nt work as expected. Lets say you have the following string variable.
    kevin@kevin@tests

    The last index of @ is 12 (Remember sql server is not a Zero based system)
    You function returns 11.

    I have made a small modification.

    CREATE FUNCTION dbo.LASTINDEX(@STRING VARCHAR(8000), @CHAR CHAR)
    RETURNS INT
    AS
    BEGIN
    RETURN LEN(@STRING) – (CHARINDEX(@CHAR, REVERSE(@STRING))-1)
    END

    Kevin

  2. there’s one more error that drove me mad for 4 hours…

    SELECT DATALENGTH(‘ssssv ‘)
    is different from
    SELECT LEN(‘ssssv ‘)

    you need to use DATALENGTH to cover the case when the string end with a space

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