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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply