March 13, 2013 at 8:44 am
Hello Everyone
Thank you all for the suggestions yesterday. I have written a SSIS package that now needs to read the file name of a text file to pump in.
I need to read the file name, split the file into pieces and use that parts of the file name for some other things. My real issue is splitting the file name and getting the parts that I need. I cannot use the splitter that is part of the tally table. I just need to have the SQL functions to work and split the file name.
This is the file name, which consists of: TypeofData_Date_Time.FileExtension
Notes_20130204_003015.txt
I would like to split the string at each underscore. I do not care about the file extension or the underscores. All I need are the other three parts. The file name will be changing everyday when the file is generated. This will be generated multiple times per day. Hence the time in the file name.
I am in need of these three parts of the file.
Notes
20130204
003015
Thank You in advance for all your assistance, suggestions and comments
Andrew SQLDBA
March 13, 2013 at 9:15 am
declare @filename varchar(100)
set @filename = 'Notes_20130204_003015.txt';
select PARSENAME(replace(@filename,'_','.'),4)
select PARSENAME(replace(@filename,'_','.'),3)
select PARSENAME(replace(@filename,'_','.'),2)
select PARSENAME(replace(@filename,'_','.'),1)
March 13, 2013 at 9:35 am
Thank You very much,
That is perfect, does exactly what is needed.
Greatly appreciate it
Thanks again
Andrew SQLDBA
March 13, 2013 at 2:56 pm
Eugene Elutin (3/13/2013)
declare @filename varchar(100)
set @filename = 'Notes_20130204_003015.txt';
select PARSENAME(replace(@filename,'_','.'),4)
select PARSENAME(replace(@filename,'_','.'),3)
select PARSENAME(replace(@filename,'_','.'),2)
select PARSENAME(replace(@filename,'_','.'),1)
Fantastic use of PARSENAME! Wow!
-- Itzik Ben-Gan 2001
March 13, 2013 at 3:32 pm
Alan.B (3/13/2013)
Eugene Elutin (3/13/2013)
declare @filename varchar(100)
set @filename = 'Notes_20130204_003015.txt';
select PARSENAME(replace(@filename,'_','.'),4)
select PARSENAME(replace(@filename,'_','.'),3)
select PARSENAME(replace(@filename,'_','.'),2)
select PARSENAME(replace(@filename,'_','.'),1)
Fantastic use of PARSENAME! Wow!
Not really. Quite slow one, actually. But will suite to the purpose of OP in this case.
Don't use it for splitting string values in select, use Jeff Moden known splitter for best performance possible with T-SQL http://www.sqlservercentral.com/articles/Tally+Table/72993/
Will I qualify for any commission for free marketing... :hehe:
March 13, 2013 at 4:01 pm
Eugene Elutin (3/13/2013)
Alan.B (3/13/2013)
Eugene Elutin (3/13/2013)
declare @filename varchar(100)
set @filename = 'Notes_20130204_003015.txt';
select PARSENAME(replace(@filename,'_','.'),4)
select PARSENAME(replace(@filename,'_','.'),3)
select PARSENAME(replace(@filename,'_','.'),2)
select PARSENAME(replace(@filename,'_','.'),1)
Fantastic use of PARSENAME! Wow!
Not really. Quite slow one, actually. But will suite to the purpose of OP in this case.
Don't use it for splitting string values in select, use Jeff Moden known splitter for best performance possible with T-SQL http://www.sqlservercentral.com/articles/Tally+Table/72993/
Will I qualify for any commission for free marketing... :hehe:
Thanks Eugene.
I have never used PARSENAME and was wondering why I did not see it used like that more often. I played around with it a little and see why... In addition to being slow it has a 4 delimeter limit. What a tease.
For fun I put together another version that dynamically calculates the delimeters...
DECLARE @filename varchar(100) = 'text1_text2.txt',
@delimeter char(1)='_';
SET @filename=replace(@filename,@delimeter,'.')
;WITH
nums(n) AS(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns),
a(n,c)AS(SELECT n, SUBSTRING(@filename,n,1) [c] FROM nums),
b(m)AS(SELECT COUNT(*) FROM a WHERE c='.')
SELECT n, PARSENAME(@filename,n) AS [part] FROM nums
CROSS APPLY b
WHERE n<=(m+1)
ORDER BY n DESC
-- Itzik Ben-Gan 2001
March 13, 2013 at 4:35 pm
Alan.B (3/13/2013)
Eugene Elutin (3/13/2013)
Alan.B (3/13/2013)
Eugene Elutin (3/13/2013)
declare @filename varchar(100)
set @filename = 'Notes_20130204_003015.txt';
select PARSENAME(replace(@filename,'_','.'),4)
select PARSENAME(replace(@filename,'_','.'),3)
select PARSENAME(replace(@filename,'_','.'),2)
select PARSENAME(replace(@filename,'_','.'),1)
Fantastic use of PARSENAME! Wow!
Not really. Quite slow one, actually. But will suite to the purpose of OP in this case.
Don't use it for splitting string values in select, use Jeff Moden known splitter for best performance possible with T-SQL http://www.sqlservercentral.com/articles/Tally+Table/72993/
Will I qualify for any commission for free marketing... :hehe:
Thanks Eugene.
I have never used PARSENAME and was wondering why I did not see it used like that more often. I played around with it a little and see why... In addition to being slow it has a 4 delimeter limit. What a tease.
For fun I put together another version that dynamically calculates the delimeters...
DECLARE @filename varchar(100) = 'text1_text2.txt',
@delimeter char(1)='_';
SET @filename=replace(@filename,@delimeter,'.')
;WITH
nums(n) AS(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns),
a(n,c)AS(SELECT n, SUBSTRING(@filename,n,1) [c] FROM nums),
b(m)AS(SELECT COUNT(*) FROM a WHERE c='.')
SELECT n, PARSENAME(@filename,n) AS [part] FROM nums
CROSS APPLY b
WHERE n<=(m+1)
ORDER BY n DESC
Please consider its intended purpose, providing Server, Database, Schema, ObjectName of database objects.
March 13, 2013 at 6:37 pm
Here's another way:
SELECT ItemNumber, Item
FROM PatternSplitCM('Notes_20130204_003015.txt', '[._]')
WHERE Matched = 0
PatternSplitCM is described in the 4th article in my signature.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply