Is there a LastIndexOf in SQL Server?

sql sql-server sql-server-2016
AngryHacker picture AngryHacker · Aug 17, 2016 · Viewed 83.7k times · Source

I am trying to parse out a value from a string that involves getting the last index of a string. Currently, I am doing a horrible hack that involves reversing a string:

SELECT REVERSE(SUBSTRING(REVERSE(DB_NAME()), 1, 
    CHARINDEX('_', REVERSE(DB_NAME()), 1) - 1))

To me this code is nearly unreadable. I just upgraded to SQL Server 2016 and I hoping there is a better way. Is there?

Answer

Gordon Linoff picture Gordon Linoff · Aug 17, 2016

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()) + '_'))

Related questions

STRING_AGG is not a recognized built-in function name

I've downloaded and installed SQL Server 2016. When I attempted to use the STRING_AGG function I receive this error. Here is my code: SELECT STRING_AGG(cast(FieldNumber AS VARCHAR(100)), ',') FROM Fields I've installed SQL Server 2016 and SP1. …

Read More
Accessing JSON Array in SQL Server 2016 using JSON_VALUE

I am stuck while accessing array inside json using newly introduced JSON_VALUE function. Please consider following code - IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='JsonData') DROP TABLE JsonData; go CREATE TABLE JsonData(JsonData nvarchar(max)); …

Read More
SSIS Error: VS_NEEDSNEWMETADATA

I'm currently updating all of our ETLs using Visual Studio 2015 (made in BIDS 2008) and redeploying them to a new reporting server running on SQL Server 2016 (originally 2008R2). While updating one of the ETLs and trying to run on the new …

Read More