October 16, 2015 at 4:26 pm
I have a filename as follows:
123_20151016_3152_AIRHtest1.txt
What I would like to extract from this fill name is the "3152" only. What is the correct way to do this?
October 23, 2015 at 7:05 am
There are many ways to do this, depending upon the data, but here are a couple of possibilities:
DECLARE @FILENAME VARCHAR(50) = '123_20151016_3152_AIRHtest1.txt';
SELECT @FILENAME = REPLACE(@FILENAME, '.txt', ''); --Remove file extension.
SELECT @FILENAME = REPLACE(@FILENAME, '_', '.'); --Replace underscores with full stops/periods.
SELECT PARSENAME(@FILENAME, 2); --Use PARSENAME function to select second element from the right.
GO
DECLARE @FILENAME VARCHAR(50) = '123_20151016_3152_AIRHtest1.txt';
SELECT SUBSTRING(@FILENAME, CHARINDEX('_',@FILENAME, 5)+1, 4) --Start at 5th character and search for underscore.
October 23, 2015 at 10:21 am
You have not explained the criteria (e.g. "I need the the number between the third and fourth underscore")...
That said, the splitter referenced in my signature will do the trick.
SELECT Item
FROM DelimitedSplit8K('123_20151016_3152_AIRHtest1.txt','_')
WHERE ItemNumber = 3;
-- Itzik Ben-Gan 2001
October 23, 2015 at 12:33 pm
I think that Alan hit the nail on the head. If you aren't familiar with DelimitedSplit8K yet, take the time to read Jeff's article. Dig in and spend the time to get familiar with it. Be forewarned that it will change the way you look at data.
If this is the type of thing you do frequently, you can save yourself some time now and make this function accessible from a centralized utility database. It will save you from having to create it in multiple databases.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply