In C#, we have String.LastIndexOf
method to get a last index of a particular character location for given string. Is there any similar function to do same in SQL Server. I tried using CHARINDEX
but couldn't able to achieve it.
Asked
Active
Viewed 9.1k times
43
![](../../users/profiles/2145980.webp)
Josh Burgess
- 8,696
- 30
- 45
![](../../users/profiles/377407.webp)
Mahender
- 5,114
- 7
- 35
- 53
-
13Hey, this "duplicated" question is for SQL Server 2008, and the other one is for SQL Server 2000. There is a difference between them. – Guillermo Gutiérrez Dec 03 '14 at 19:53
-
1So add an answer to the other question. – JasonMArcher Dec 03 '14 at 20:26
3 Answers
75
A little tricky, but you could do something like:
REVERSE(SUBSTRING(REVERSE([field]),0,CHARINDEX('[char]',REVERSE([field]))))
![](../../users/profiles/959508.webp)
antinescience
- 2,299
- 21
- 30
-
I had this stashed from http://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql, because it seems to come up pretty often. – antinescience Mar 29 '13 at 19:05
-
2The answer here is more concise and efficient: http://stackoverflow.com/a/13610627/197591 – Neo Jan 29 '16 at 07:23
27
DECLARE @x VARCHAR(32) = 'xyzxyzyyythgetdghydgsh';
SELECT LEN(@x) - CHARINDEX('y', REVERSE(@x)) + 1;
![](../../users/profiles/61305.webp)
Aaron Bertrand
- 242,666
- 35
- 420
- 451
0
Try this one out, analyze result step by step.
declare @string varchar(max)
declare @subString varchar(max)
set @string = 'this is a duplicated question, but may get some new answers, since tech chagne from time to time as we know';
set @subString = 'this is a duplicated question, but may get some new answers, since tech chagne from time to time'
--Find the string.lastIndexof(time)
select LEN(@String)
select LEN(@SubString)
select CHARINDEX('time', REVERSE(@string))
select reverse(@string)
select reverse('time')
SELECT LEN(@string) - CHARINDEX(reverse('time'), REVERSE(@string)) - Len('time') + 1
![](../../users/profiles/2141354.webp)
ljh
- 2,438
- 1
- 12
- 20