SQL String with DTS, using split string
Posted on 2014-01-08
I have a derived Column transformation in my DTS package that gets a Job number using an expression:
SUBSTRING(@[User::CurrentExcelPath],51,12) where (@[User::CurrentExcelPath] contains the filepath.
My job numbers have changed where length will not work.
I need to change to a split string.
My End goal is to strip out the job numbers from a given path. The below is 2 examples of a path
W:\ProcessFiles\Labor_Sheet_Importation_123\ JOBNO_2014-01-01014_11331_URI- Daily Labor-Culpepper Recon 1-2-14.xlsm
W:\ProcessFiles\Labor_Sheet_Importation_123\ JOBNO_2014-01-0001-RECON_11331_URI- Daily Labor-Culpepper Recon 1-3-14.xlsm
end result for Job Number will be:
How can I change this to strip out the job Number using a split string and will
derived column transformation Expression accept a split string?
The number of underscores will always be the same, 3, but the lenght may vary from 12 to 18 now.