CODEWITHSUNDEEP
sqlsql-servert-sql
SeanKilleen
SeanKilleen

Reputation: 8977

SQL server query: remove text between the last index of two characters?

The Problem

I have a field that stores file keys, such as:

dev/application/document_type_name/document 12345-67890_123.pdf

I need to select the key without the number on the end so the value looks like:

dev/application/document_type_name/document 12345-67890_.pdf

Potential Strategy

It's been a while since I've done T-SQL but coming from the .NET side I think the general strategy would be:

In C#, I think it would be something like:

var test = "dev/application/document_type_name/document 12345-67890_123.pdf"
var indexOfUnderscore = test.LastIndexOf("_");
var indexOfPeriod = test.LastIndexOf(".");
var textToReplace = subtring(indexOfUnderscore + 1, indexOfPeriod -1);
var output = test.Replace(textToReplace, String.Empty);

Notes:

Upvotes: 0

Views: 2432

Answers (1)

podiluska
podiluska

Reputation: 51494

Try 

select left(@s,len(@s)-charindex('_',REVERSE(@s)))+'_.pdf'

where @s is your string. Or if the file extension can change

select left(@s,len(@s)-charindex('_',REVERSE(@s))+1) 
     + right(@s,charindex('.',REVERSE(@s)))

Upvotes: 6

Related Questions