Extract a uniform pattern from a file path

Question

Extract a uniform pattern from a file path

pnr8uk

SSCommitted

Points: 1828
More actions
October 10, 2016 at 1:20 am

#313788

Hi All
I have a strange problem here which I can't quite get to the bottom of.
I have a file path in my table which reads like this:
\\cc0dware01\BMF\RMS\REBATERATES-WORKING-2016ToEnd08-20160905180112.csv
I only want to extract 20160905180112 from the string, the name format is strict and will always be the same.
Despite trying many things LEFT, REVERSE, CHARINDEX and getting very close I can't quite pull out the part I want every time.
Anyone got an easy fix for this?
Any help greatly appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

pnr8uk (10/10/2016)
Hi All
I have a strange problem here which I can't quite get to the bottom of.
I have a file path in my table which reads like this:
\\cc0dware01\BMF\RMS\REBATERATES-WORKING-2016ToEnd08-20160905180112.csv
I only want to extract 20160905180112 from the string, the name format is strict and will always be the same.
Despite trying many things LEFT, REVERSE, CHARINDEX and getting very close I can't quite pull out the part I want every time.
Anyone got an easy fix for this?
Any help greatly appreciated.

Quick suggestion

😎

DECLARE @INPUT_STR VARCHAR(1024) = '\\cc0dware01\BMF\RMS\REBATERATES-WORKING-2016ToEnd08-20160905180112.csv';

SELECT

LEFT(RIGHT(@INPUT_STR,CHARINDEX(CHAR(45),REVERSE(@INPUT_STR),1) - 1),14) AS OUT_STR;

Output

OUT_STR

--------------

20160905180112

pnr8uk SSCommitted Points: 1828 More actions · Answer 2

Wow thank you that is amazingly simple.

Many thanks again

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

Eirikur Eiriksson

SSC Guru

Points: 182915

October 10, 2016 at 2:46 am

#1905607

You are very welcome.

😎

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

This might be is simpler.

DECLARE @INPUT_STR VARCHAR(1024) = '\\cc0dware01\BMF\RMS\REBATERATES-WORKING-2016ToEnd08-20160905180112.csv';

SELECT SUBSTRING( @INPUT_STR, LEN( @INPUT_STR) - 17, 14);

I wonder what are the varying parts of the file path. Is it only the path? Maybe WORKING can change to something else? Or maybe the time period? Knowing what can change, we can be sure to prevent any problems.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 5

Looks like a time code (fixed at 14 characters)... The following will work even if the file path changes...

DECLARE @input VARCHAR(1000) = '\\cc0dware01\BMF\RMS\REBATERATES-WORKING-2016ToEnd08-20160905180112.csv';

SELECT output = LEFT(RIGHT(@input, 18), 14);

ScottPletcher SSC Guru Points: 101120 More actions · Answer 6

As long as the file name is the only YYYYMMDDHHMMSS string in the file, I think you can just check for that pattern:

SELECT input, SUBSTRING(input, PATINDEX('%[2][01][0-9][0-9][01][0-9][012][0-9][0-5][0-9][0-5][0-9]%', input), 14) AS extract

FROM (

VALUES('\\cc0dware01\BMF\RMS\REBATERATES-WORKING-2016ToEnd08-20160905180112.csv'),

('\\cc0dware01\BMF\RMS\REBATERATES-WORKING-2016ToEnd08-20160905180113.xlsx'),

('c:\20160905180113.')

) AS test_data(input)

Edit: Added SQL code block.

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".