August 8, 2012 at 2:54 pm
hey guys,am trying to write a sql to get the datepart from a filename in sql, the filename always varies in the table for example; i have a file name like this "TRANS_SERVER_M_20120731_01.txt",i want to get only the part of that string which has the date 20120731,note that the fliename will always vary so if you wanted to use a substring to get the date part, the location for which the substring would use as its start length would always vary;meaning some could be like this "TRANS_01_20120731.txt",so i want to be able to write code such that no matter the length of the file name or where the start positon of the date part, it would always return the datepart. either by using a scalar function in sql or just plain sql. thanks
any help is welcome thanks guys
August 8, 2012 at 3:45 pm
Will the date always be 8 digits with no punctuation, like dashes or underscores?
August 8, 2012 at 3:54 pm
In addition to the above question, completely variable names could end up with something like this:
ABC_20120605_Rewrite_20120701_abcd.def
Which date gets used in something like that, or are you guaranteed to only have a single unbroken 8 digit string component that will ever evaluate as a date?
The quick answer to your question, however, is to use a string splitter, such as the delimited8k. This will break each element in the name into its components... in this case you break on _'s and .'s instead of ,'s.
Once you've got that, you process with IsDate() to figure out which piece is the date element(s), and go from there.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 8, 2012 at 6:04 pm
You can use this in a stored procedure or turn it into a table-valued function.
DECLARE
@s-2 VARCHAR(MAX)
,@Split CHAR(1)
,@X XML
SET @s-2 = 'TRANS_SERVER_M_20120731_01.txt'
--SET @s-2 = 'ABC_20120605_Rewrite_20120701_abcd.def'
SET @Split = '_'
SELECT
@X = CONVERT(XML,'<root><s>' + REPLACE(@S,@Split,'</s><s>')+'</s></root>')
SELECT
(CASE
WHEN ISDATE(Value) = 1 THEN CAST(Value AS DATE)
ELSE NULL
END) AS DateValue
FROM
(
SELECT T.c.value('.','VARCHAR(MAX)') AS [Value] FROM @X.nodes('/root/s') T (c)
) Result
WHERE
ISDATE(Value) = 1
August 8, 2012 at 8:50 pm
Steven Willis (8/8/2012)
You can use this in a stored procedure or turn it into a table-valued function.
DECLARE
@s-2 VARCHAR(MAX)
,@Split CHAR(1)
,@X XML
SET @s-2 = 'TRANS_SERVER_M_20120731_01.txt'
--SET @s-2 = 'ABC_20120605_Rewrite_20120701_abcd.def'
SET @Split = '_'
SELECT
@X = CONVERT(XML,'<root><s>' + REPLACE(@S,@Split,'</s><s>')+'</s></root>')
SELECT
(CASE
WHEN ISDATE(Value) = 1 THEN CAST(Value AS DATE)
ELSE NULL
END) AS DateValue
FROM
(
SELECT T.c.value('.','VARCHAR(MAX)') AS [Value] FROM @X.nodes('/root/s') T (c)
) Result
WHERE
ISDATE(Value) = 1
You might need a bit more criteria because ISDATE isn't going to do it for you. Try this and see...
SELECT ISDATE('3519')
--Jeff Moden
Change is inevitable... Change for the better is not.
August 8, 2012 at 10:07 pm
klineandking (8/8/2012)
hey guys,am trying to write a sql to get the datepart from a filename in sql, the filename always varies in the table for example; i have a file name like this "TRANS_SERVER_M_20120731_01.txt",i want to get only the part of that string which has the date 20120731,note that the fliename will always vary so if you wanted to use a substring to get the date part, the location for which the substring would use as its start length would always vary;meaning some could be like this "TRANS_01_20120731.txt",so i want to be able to write code such that no matter the length of the file name or where the start positon of the date part, it would always return the datepart. either by using a scalar function in sql or just plain sql. thanksany help is welcome thanks guys
I'll be the first to say this isn't elegant by any means (and a couple obvious flaws), and I certainly wouldn't recommend running it against a million rows, but if you use it as a function it should work just fine to parse out the filename and return the 8-digit date:ALTER FUNCTION fx_Get8DigitDate (
@Text varchar(50)
)
RETURNS VARCHAR(8) AS
BEGIN
DECLARE @Position tinyint
SET @Position = PATINDEX('%[2-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', @Text)
RETURN SUBSTRING(@Text, @Position, 8)
END
Test
SELECT dbo.fx_Get8DigitDate('TRANS_SERVER_M_20120731_01.txt')
SELECT dbo.fx_Get8DigitDate('TR_20120808_ANS_01_201_20731.txt')
SELECT dbo.fx_Get8DigitDate('TRANS_01_20120731.txt')
SELECT dbo.fx_Get8DigitDate('TRANS_01_20120731559_123.txt')
Results:
20120731
20120808
20121231
20121122
Or just use it inline (pretty ugly though)
SELECT SUBSTRING('TRANS_01_20120731559_123.txt', PATINDEX('%[2-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', 'TRANS_01_20120731559_123.txt'), 8)Result:
20120731
Would something like this work for you?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 8, 2012 at 11:12 pm
Jeff Moden (8/8/2012)
You might need a bit more criteria because ISDATE isn't going to do it for you. Try this and see...
SELECT ISDATE('3519')
Good point, but I made a quick assumption that there was going to be at least one or more valid date in the string somewhere. A quick check of the element length can weed out any non-date integers and then the ISDATE function will cause the element to be NULL if it's still not a valid date (such as being out-of-range). Obviously the developer should know what general pattern to expect...but of course we all know what is said about assumptions! 😉
OK, just for fun here's a tweak that checks for the length of the input elements and also if more than one date then only the most recent date is returned. (Just as an example.)
DECLARE
@s-2 VARCHAR(MAX)
,@Split CHAR(1)
,@X XML
SET @s-2 = 'TRANS_SERVER_M_20120731_01.txt' -- good date
--SET @s-2 = 'ABC_20120605_Rewrite_20120701_abcd.def' -- two good dates, most recent returned
--SET @s-2 = 'ABC_20120605_Rewrite_3519_abcd.def' -- good date, bad date is ignored (null)
--SET @s-2 = 'TRANS_SERVER_M_3519_01.txt' -- no good dates, returns null
--SET @s-2 = 'TRANS_SERVER_M_20120732_01.txt' -- out of range date, returns null
SET @Split = '_'
SELECT
@X = CONVERT(XML,'<root><s>' + REPLACE(@S,@Split,'</s><s>')+'</s></root>')
SET ROWCOUNT 1
SELECT
DateValue
FROM
(
SELECT
(CASE
WHEN LEN(Value) = 8 AND ISDATE(Value) = 1 THEN CAST(Value AS DATE)
ELSE NULL
END) AS DateValue
FROM
(
SELECT T.c.value('.','VARCHAR(MAX)') AS [Value] FROM @X.nodes('/root/s') T (c)
) Result1
WHERE
ISDATE(Value) = 1
) Result2
WHERE
DateValue IS NOT NULL
ORDER BY
DateValue DESC
SET ROWCOUNT 0
August 9, 2012 at 12:15 am
Here's a couple of ways, obviously fraught with hazards if you don't have a lot of control over the creation of the filenames:
DECLARE @Table TABLE (filename VARCHAR(200))
DECLARE @CurrentYear CHAR(4) = '2012'
INSERT INTO @Table
SELECT 'TRANS_01_20120731.txt'
UNION ALL SELECT 'TRANS_SERVER_M_20120731_01.txt'
-- Both rely on 2012 (the current year) being present in the filename only once
SELECT
-- Locate current year within the string and use that
Method1=SUBSTRING(filename, CHARINDEX(@CurrentYear, filename), 8)
-- Use a string splitter like DelimitedSplit8K from Jeff Moden
,Method2=item
FROM @Table
CROSS APPLY dbo.DelimitedSplit8K(REPLACE(filename COLLATE LATIN1_GENERAL_BIN, '_', '.'), '.')
WHERE LEFT(item, 4) = @CurrentYear
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
August 9, 2012 at 8:10 am
thank you so much i will try it and give you an answer
August 9, 2012 at 8:17 am
thank you so much your code really helped
August 9, 2012 at 8:18 am
thanks for all the input i really appreciate it
August 9, 2012 at 8:18 am
i think this will work just fine thank you so much
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply