Last occurrence of character in stri

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Last occurrence of character in stri

Author

Topic

avimandale
Starting Member

1 Post

Posted - 2008-01-28 : 10:34:21

How to locate last occurrence of character in string

charindex() function lets you find first occurrence of a particular
character in a string.
Is there similar function to find last occurrence of character in
string.

I could use reverse() or some script to find that, but it will be
inefficient.
Could anybody suggest me efficient solution for this.

Thanks and Regards,
Avin

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-28 : 18:11:54

I don't know anyway off the top of my head to do what you want they way you want. But, here is some code that does it with a REVERSE. Maybe someone else has some ideas...?

DECLARE @Table TABLE(String VARCHAR(10))

INSERT @Table
SELECT 'abcdef'
UNION ALL SELECT 'defgheij'
UNION ALL SELECT 'wxyz'

DECLARE @SearchString VARCHAR(1)
SET @SearchString = 'e'

SELECT
	CASE
		WHEN PATINDEX('%[' + @SearchString + ']%', REVERSE(String)) > 0
			THEN LEN(String) - PATINDEX('%['+ @SearchString + ']%', REVERSE(String)) + 1
		ELSE 0
	END AS LastPosition
FROM
	@Table


SELECT
	CASE
		WHEN CHARINDEX(@SearchString, REVERSE(String)) > 0
			THEN LEN(String) - CHARINDEX(@SearchString, REVERSE(String)) + 1
		ELSE 0
	END AS LastPosition
FROM
	@Table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources