Query to fetch specific part from a filepath

I have filename field in table which contains value as follows,

Its image files preceding with employee code

H:KYC_PDF_Cloud_NEmployeePANCard-CroppedSignaturese45432_Sign.jpg
H:KYC_PDF_Cloud_NEmployeePANCard-CroppedSignaturese45434_1418_Sign.jpg
H:KYC_PDF_Cloud_NEmployeePANCard-CroppedSignaturesE45434_age_Sign.jpg
H:KYC_PDF_Cloud_NEmployeePANCard-CroppedSignaturesE45538_Rana_Sign.jpg

Now I want to write SQL query in such a way that I will get only employee numbers from the path i.e. for example output will be in the following format

empno
e45432
e45434
E45538

I have tried with following query

 select SUBSTRING([FileName],55,6) as empno  from #tmp_filepath

it has given me the output I wanted but it’s like I’m hardcoding the positions

Answer

Try this:

SELECT 
LEFT(
     RIGHT(Str,CHARINDEX('',REVERSE(Str))-1),
     CHARINDEX('_',RIGHT(Str,CHARINDEX('',REVERSE(Str))-1))-1
  )
FROM T

SQLFiddle demo

Advertisement

Answer