Query to fetch specific part from a filepath

Question

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

i'm not sure about this but you need something of this kind

vhadalgi
– vhadalgi

2013-12-24 07:25:05 +00:00
Commented Dec 24, 2013 at 7:25 — vhadalgi
– vhadalgi, Commented Dec 24, 2013 at 7:25

valex · Accepted Answer · 2013-12-24 07:31:19Z

3

Try this:

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

SQLFiddle demo

answered Dec 24, 2013 at 7:31

valex

24.2k7 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

shweta Over a year ago

declare @str varchar(50) select @str=right('H:\KYC_PDF_Cloud_N\Employee\PANCard-CroppedSignatures\E35697_Sign.jpg', CHARINDEX('\',reverse('H:\KYC_PDF_Cloud_N\Employee\PANCard-CroppedSignatures\E35697_Sign.jpg'))-1) select left(@str,charindex('_',@str)-1)

Collectives™ on Stack Overflow

Query to fetch specific part from a filepath

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related