Substring

Question

Substring

hoseam

SSCertifiable

Points: 5533
More actions
September 22, 2017 at 6:06 am

#332730

I get a files with a dates e.g Generate20170922, These files are stored like this in the db. I want to select File names only, I tried Substring
substring(replace(sourceFileName,'2','') ,0,charindex('.',replace(sourceFileName,'2','')) ) FileName
But it removes only the 2. please help

Viewing 9 posts - 1 through 8 (of 8 total)

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

hoseam - Friday, September 22, 2017 6:06 AM
I get a files with a dates e.g Generate20170922, These files are stored like this in the db. I want to select File names only, I tried Substring
substring(replace(sourceFileName,'2','') ,0,charindex('.',replace(sourceFileName,'2','')) ) FileName
But it removes only the 2. please help

DECLARE @Filename VARCHAR(20) = 'Generate20170922';

SELECT @Filename , LEFT(@Filename, LEN(@Filename) - 8);

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sgmunson SSC Guru Points: 110640 More actions · Answer 2

Try this:
STUFF(sourceFileName, CHARINDEX('2', sourceFileName), LEN(sourceFileName) - CHARINDEX('2', sourceFilename) + 1, '') AS FileName

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

hoseam SSCertifiable Points: 5533 More actions · Answer 3

How then do I SELECT only datime from this:

20170913100320

Thom A SSC Guru Points: 99133 More actions · Answer 4

hoseam - Friday, September 22, 2017 7:22 AM
How then do I SELECT only datime from this:
20170913100320

Considering that your file name was previously "Generate20170922", your latest post seems to imply that you could have a variable length for the value of the datetime in the file name. Is this the case? Which format are all your filenames in? Could you provide a few examples?

Thanks,

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sgmunson SSC Guru Points: 110640 More actions · Answer 5

hoseam - Friday, September 22, 2017 7:22 AM
How then do I SELECT only datime from this:
20170913100320

Assuming there are always exactly 14 datetime characters, meaning YYYYMMDDHHMMSS, then you can use the following:
SELECT DATETIMEFROMPARTS(LEFT(DT.DT_String, 4), SUBSTRING(DT.DT_String, 5, 2), SUBSTRING(DT.DT_String, 7, 2), SUBSTRING(DT.DT_String, 9, 2), SUBSTRING(DT.DT_String, 11, 2), SUBSTRING(DT.DT_String, 13, 2), 0) AS DT FROM ( VALUES ('Generate20170913100320') ) AS X (sourceFileName) CROSS APPLY ( VALUES (RIGHT(X.sourceFileName, 14)) ) AS DT (DT_String);

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Thom A - Friday, September 22, 2017 7:59 AM
hoseam - Friday, September 22, 2017 7:22 AM
How then do I SELECT only datime from this:
20170913100320
Considering that your file name was previously "Generate20170922", your latest post seems to imply that you could have a variable length for the value of the datetime in the file name. Is this the case? Which format are all your filenames in? Could you provide a few examples?
Thanks,

To that end, are the file names also devoid of an extension?

@hoseam... the only way people can help without having to try to take a bite of a rolling donut is for you to actually and completely define the problem. In this case, that probably means you listing several file names that you actually have.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hoseam SSCertifiable Points: 5533 More actions · Answer 7

Thom A - Friday, September 22, 2017 7:59 AM
hoseam - Friday, September 22, 2017 7:22 AM
How then do I SELECT only datime from this:
20170913100320
Considering that your file name was previously "Generate20170922", your latest post seems to imply that you could have a variable length for the value of the datetime in the file name. Is this the case? Which format are all your filenames in? Could you provide a few examples?
Thanks,

They are csv files, e.g File 1, Name: Generate20170913100320; File 2, Name: Generate20170914110420

Thom A SSC Guru Points: 99133 More actions · Answer 8

hoseam - Tuesday, September 26, 2017 1:04 AM
They are csv files, e.g File 1, Name: Generate20170913100320; File 2, Name: Generate20170914110420

So, based on your (couple of) examples, no they do not have an extension and always use 14 characters to represent the date (at the end of the file name)? If so, then you only need to use the RIGHT function (RIGHT (Transact-SQL)). Have a go yourself and if you get stuck post back what you tried, along with examples, and why the results don't return what you expect.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk