February 9, 2016 at 12:26 pm
Hello all,
I have a table with a field called FILE_NAME. This field contains a string of text: 001193655_968966171_20160201_05071116_814.out
I need a query to extract 20160201 from this string. I have racked my brain and searched for solutions to no avail. Please help.
February 9, 2016 at 12:31 pm
Maybe code below, if you're looking for a date:
SELECT CASE WHEN PATINDEX('%[_]20[0-9][0-9][0-1][0-9][0-3][0-9][_]%', string) = 0
THEN ''
ELSE SUBSTRING(string, PATINDEX('%[_]20[0-9][0-9][0-1][0-9][0-3][0-9][_]%', string) + 1, 8)
END AS date
FROM (
SELECT '001193655_968966171_20160201_05071116_814' AS string
) AS test_data
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 9, 2016 at 12:48 pm
Just this piece works:
SUBSTRING(FILE_NAME, PATINDEX('%[_]20[0-9][0-9][0-1][0-9][0-3][0-9][_]%', FILE_NAME) + 1, 8)
Thanks for the help!
February 9, 2016 at 6:24 pm
Here's another approach if you do this type of thing frequently.
SELECT s.Item
FROM dbo.table_name t
CROSS APPLY dbo.DelimitedSplit8K(t.file_name, '_') s
WHERE s.ItemNumber = 3;
This may be a bit much for such a simple split, but Jeff's ITVF is one I think almost everyone should have available. See http://www.sqlservercentral.com/articles/Tally+Table/72993/ for the article. It isn't short, but I think it's worth it.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy