CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
shweta
shweta

Reputation: 319

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_N\Employee\PANCard-CroppedSignatures\e45432_Sign.jpg
H:\KYC_PDF_Cloud_N\Employee\PANCard-CroppedSignatures\e45434_1418_Sign.jpg
H:\KYC_PDF_Cloud_N\Employee\PANCard-CroppedSignatures\E45434_age_Sign.jpg
H:\KYC_PDF_Cloud_N\Employee\PANCard-CroppedSignatures\E45538_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

Upvotes: 0

Views: 349

Answers (1)

valex
valex

Reputation: 24144

Try this:

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

SQLFiddle demo

Upvotes: 3

Related Questions