Is There a Lastindexof in SQL Server

Is there a LastIndexOf in SQL Server?

If you want everything after the last _, then use:

select right(db_name(), charindex('_', reverse(db_name()) + '_') - 1)

If you want everything before, then use left():

select left(db_name(), len(db_name()) - charindex('_', reverse(db_name()) + '_'))

Find index of last occurrence of a sub-string using T-SQL

You are limited to small list of functions for text data type.

All I can suggest is start with PATINDEX, but work backwards from DATALENGTH-1, DATALENGTH-2, DATALENGTH-3 etc until you get a result or end up at zero (DATALENGTH-DATALENGTH)

This really is something that SQL Server 2000 simply can't handle.

Edit for other answers : REVERSE is not on the list of functions that can be used with text data in SQL Server 2000

What is best way to get last indexof character in SQL 2008

A little tricky, but you could do something like:

REVERSE(SUBSTRING(REVERSE([field]),0,CHARINDEX('[char]',REVERSE([field]))))

Using LastIndexOf and SubString in mssql

SELECT Id,
       RIGHT(Code, CHARINDEX('.', REVERSE('.' + Code)) - 1) AS [Result],
       Code
FROM   YourTable 

Getting everything after last '/'

I would use an approach of finding how many characters you need to use from the right. I would do this by first reversing the string and then searching for the '/'. This will tell you how many characters from the right this '/' is. I would then use this in the RIGHT function:

SQL Fiddle

MS SQL Server 2017 Schema Setup:

Query 1:

DECLARE @documentName varchar(100) = 'Uploads/Documents/6093/12/695-Graco-SW_5-15-19.pdf'

SELECT RIGHT(@documentName, CHARINDEX('/',REVERSE(@documentName))-1)

Results:

|                          |
|--------------------------|
| 695-Graco-SW_5-15-19.pdf |

Select everything after a character in SQL Server

If all the string starts with X then you just can select substring starting from second character as below:

select substring('X1234',2,len('X1234'))

Or if there is chance to start with other characters then first you can use case when check whether the first character is X or not.

select case when left('X1234',1)='X' then substring('X1234',2,len('X1234'))end

Getting last group of number from string

1. reverse() the string
2. use patindex() to find non numeric character
3. use right() to extract the characters

---

SELECT RIGHT(INBOX_COL, PATINDEX('%[^0-9]%', REVERSE(INBOX_COL)) - 1)

Get values after '.'

Perhaps the simplest way is to use string_split():

select s.value
from t cross apply
     string_split(t.log, '.') s
where t.log like '%.' + s.value;

Here is a db<>fiddle.

How to remove the last digit after the some symbols in SQL?

In SQL Server, you can use:

select left(col, len(col) - charindex('.', reverse(col)))

This is dynamic and simply assumes that there is at least on '.' in the column.

Getting rid of characters in a string

You can do :

select col, replace(col, right(col, charindex('/', reverse(col) + '/' ) -1), '')
from table t;

This assumes Folders always contains filenames.

---

Related Topics