Reverse substring-CodePudding

I am trying to extract some characters from a string. For Example in (316409953_ND_02142022_000001.pdf) I need the characters after the last underscore _ and before the "." Answer: 000001 @test = 316409953_ND_02142022_000001.pdf

I have tried:

REVERSE(SUBSTRING(REVERSE(test),CHARINDEX('.',REVERSE(test)) 1,CHARINDEX('_',REVERSE(test) 1)))

I need help with the last part of substring. First error I am getting is "Conversion failed when converting the varchar value 'fdp.100000_22024120_DN_359904613' to data type int." Secondly I need it to pick only after the last "_" Please guide or let me know if there's another way to do this.

CodePudding user response：

This looks like SQL Server.

On that assumption, and using the single example value, you can try the follolwing:

declare @test varchar(50) = '316409953_ND_02142022_000001.pdf'

select @test OriginalValue,
   ParseName(Right(@test, CharIndex('_',Reverse(@test))-1),2) ExtractedValue;

CodePudding user response：

it isn't pretty but it does the job ;P

declare @str varchar(200) = '316409953_ND_02142022_000001.pdf'
declare @tempStr varchar(200)= (select SUBSTRING(@str,LEN(@str)   2 - CHARINDEX('_',REVERSE(@str)),LEN(@str)))
set @tempStr = SUBSTRING(@tempStr,1,CHARINDEX('.',@tempStr) - 1)
select @tempStr