June 22, 2017 at 2:02 pm
FilePath Structure:
\\Servername\Temp\Foldername\ABCabcprices-20170621122040.txt
From the given filepath need to extract "ABC" from "ABCabcprices-20170621122040.txt".
Conditions : Severname and foldername can be any length and filename can be anything but need only first few characters of the filename.
June 22, 2017 at 2:08 pm
komal145 - Thursday, June 22, 2017 2:02 PMFilePath Structure:
\\Servername\Temp\Foldername\ABCabcprices-20170621122040.txtFrom the given filepath need to extract "ABC" from "ABCabcprices-20170621122040.txt".
Conditions : Severname and foldername can be any length and filename can be anything but need only first few characters of the filename.
Use a splitter, split on the backslash character and select the item with the highest item number to get the file name.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 22, 2017 at 2:12 pm
DECLARE @String VARCHAR(100) = '\\Servername\Temp\Foldername\ABCabcprices-20170621122040.txt'
SELECT SUBSTRING(@String, LEN(@String) - CHARINDEX('\', REVERSE(@String)) + 2, 3)
June 22, 2017 at 2:14 pm
I 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)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 22, 2017 at 2:26 pm
Sean 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)
This gives the entire file name. OP only wants the first few letters.
June 22, 2017 at 2:36 pm
Sean 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?
June 22, 2017 at 2:40 pm
ryanbesko - Thursday, June 22, 2017 2:26 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)
This gives the entire file name. OP only wants the first few letters.
You are right they don't want the entire filename but it isn't really clear from the OP what they really want. But as a guess they want the first three so simply add a LEFT to this solves that. But what they really want could be any number of things. I can come up with a dozen or so convoluted rules here.
SELECT LEFT(RIGHT(@FilePath, CHARINDEX('\', REVERSE(@FilePath)) - 1), 3)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 22, 2017 at 2:42 pm
komal145 - 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. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
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.
June 22, 2017 at 2:53 pm
Didn't know this was SSIS. Here is the T-SQL from my first post translated:
SUBSTRING(@[User::FilePath], LEN(@[User::FilePath]) - FINDSTRING(REVERSE(@[User::FilePath]), "\\", 1) + 2, 3)
June 22, 2017 at 5:10 pm
This is simple in SSIS, and had you posted the question in the Integration Services forum, you would have had an answer by now.
Use something like (untested)TOKEN([Filepath],"\",TOKENCOUNT([filepath]) - 1)
replace [Filepath] with your variable/column name. Can't remember whether the literal backslash has to be escaped ... you'll have to check that.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply