Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL Extract String RE: Extract String
June 22, 2017 at 2:46 pm
Sean Lange - Thursday, June 22, 2017 2:42 PMkomal145 - Thursday, June 22, 2017 2:36 PMSean Lange - Thursday, June 22, 2017 2:14 PMI am a huge fan of Jeff Moden's splitter but in this case I think it is a bit overkill. Since you know you always want everything after the last "\" we can do some pretty simple string manipulation. Keep in mind that if you have rows where there is no backslash this will fail and would need to be adjusted slightly.
DECLARE @FilePath VARCHAR(100) = '\\Servername\Temp\Foldername\ABCabcprices-20170621122040.txt'
SELECT RIGHT(@FilePath, CHARINDEX('\', REVERSE(@FilePath)) - 1)
ryanbesko - Thursday, June 22, 2017 2:12 PMDECLARE @String VARCHAR(100) = '\\Servername\Temp\Foldername\ABCabcprices-20170621122040.txt'
SELECT SUBSTRING(@String, LEN(@String) - CHARINDEX('\', REVERSE(@String)) + 2, 3)This works fine in TSQL . I am trying to do this in SSIS expression and throws error for single quotes and also charindex. Is there any equivalent to charindex in ssis?
Well in SSIS you would use the column value or the actual file name, not a scalar variable so you wouldn't need any single quotes. Knowing this was for SSIS would have made a big difference up front. Since it is posted in 2016 Development we would assume it isn't for SSIS. 🙂
OK. I was trying to do in TSQL and also SSIS.