Extract date from String

Question

Extract date from String

SQL Server

Old Hand

Points: 377
More actions
January 24, 2020 at 6:32 pm
Go to Answer
#3720257
Folks:
I have to extract the date from this string which is between files_ and .xls
I have this SELECT query but it also returns iles_ don't know where I am wrong here.
```
DECLARE @Text nvarchar(250) = '\\ABC.dns.com\file_path1\filepath2\inc_files_20200122.xls'

SELECT SUBSTRING(STUFF(@Text, 1, CHARINDEX('files_',@Text), ''), 0, CHARINDEX('.xls', STUFF(@Text, 1, CHARINDEX('files_',@Text), ''))) 
```
Output should be: 20200122

Thanks!
SQL Server

Old Hand

Points: 377
More actions
January 24, 2020 at 7:25 pm
Answer
#3720269

select Replace(Reverse(stuff(reverse(@Text),CHARINDEX(reverse('files_'),reverse(@Text)),len(@Text),'')),'.xls','')

Viewing 14 posts - 1 through 13 (of 13 total)

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

Steve Collins SSCrazy Points: 2174 More actions · Answer 1

You marked your own answer as the answer? Nicely done!

declare
  @text                            nvarchar(250)='\\abc.dns.com\file_path1\filepath2\inc_files_20200122.xls';
declare
  @rev_first_slash                int=charindex(reverse('files_'), reverse(@text))-2;
declare
  @start                        int=(len(@text)-@rev_first_slash),
  @end                            int=(@rev_first_slash-len('.xls')+1);

select substring(@text, @start, @end);

This reply was modified 4 years, 11 months ago by Steve Collins. Reason: now looks for reverse('_files')

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

crow1969 Hall of Fame Points: 3001 More actions · Answer 2

The problem is that charindex will return the position of the beginning of the token (files_), rather than the position of the end of the token. If you add 6 (the length of the token "files_"), then you get what you need.

DECLARE @Text nvarchar(250) = '\\ABC.dns.com\file_path1\filepath2\inc_files_20200122.xls'

SELECT SUBSTRING(STUFF(@Text, 1, CHARINDEX('files_',@Text) + 6, ''), 0, CHARINDEX('.xls', STUFF(@Text, 1, CHARINDEX('files_',@Text) + 6, '')))

jonathan.crawford SSCertifiable Points: 6613 More actions · Answer 3

yeah, I don't think you need all that processing either, if your file format will always be <something>_files_YYYYMMDD.xls

SELECT SUBSTRING(@Text, PATINDEX('%.xls%',@Text) - 8,8)

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Steve Collins SSCrazy Points: 2174 More actions · Answer 4

select substring(@text, len(@text)-11, 8);

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 5

 SELECT LEFT(RIGHT(@Text,12),8);

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Jeff Moden wrote:

 SELECT LEFT(RIGHT(@Text,12),8);

Simple and to the point. I would put a comment with it to explain what is being so that someone with less SQL knowledge would understand what was being done. Another way of saying this is to be able to recreate the code from the comment if the code itself were deleted. That doesn't mean put the the code in a comment.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 7

Lynn Pettis wrote:

Jeff Moden wrote:
 SELECT LEFT(RIGHT(@Text,12),8);
Simple and to the point. I would put a comment with it to explain what is being so that someone with less SQL knowledge would understand what was being done. Another way of saying this is to be able to recreate the code from the comment if the code itself were deleted. That doesn't mean put the the code in a comment.

Thanks, Lynn. @scdecade had the right idea, as well. Heh... You know me well... I normally comment everything. Since the OP never told us why he needs to get the date, I got lazy and responded in kind.

--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)

parmsivan.kannan Grasshopper Points: 20 More actions · Answer 8

DECLARE @Text nvarchar(250) = '\\ABC.dns.com\file_path1\filepath2\inc_files_20200122.xls'

SELECT SUBSTRING(REVERSE(@Text),CHARINDEX('.',REVERSE(@Text))+1,CHARINDEX('_',REVERSE(@Text))-CHARINDEX('.',REVERSE(@Text))-1)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

parmsivan.kannan wrote:

DECLARE @Text nvarchar(250) = '\\ABC.dns.com\file_path1\filepath2\inc_files_20200122.xls'
SELECT SUBSTRING(REVERSE(@Text),CHARINDEX('.',REVERSE(@Text))+1,CHARINDEX('_',REVERSE(@Text))-CHARINDEX('.',REVERSE(@Text))-1)

This is much simpler and does not require the use of REVERSE: SELECT LEFT(RIGHT(@Text,12),8);

parmsivan.kannan Grasshopper Points: 20 More actions · Answer 10

Awesome!!!

Very nice to understand.

But,I do not want to to hard code value 12 and 8. This is what i used that Reverse function.

(Sometime there is a chance in the filename contains .XLSX)

Thank You

This reply was modified 4 years, 11 months ago by parmsivan.kannan.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 11

parmsivan.kannan wrote:

Awesome!!!
Very nice to understand.
But,I do not want to to hard code value 12 and 8. This is what i used that Reverse function.
(Sometime there is a chance in the filename contains .XLSX)
Thank You

It will be much more efficient to "conditionally hardcode" based on the extension rather than to use several reverse/charindex combinations.

--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)

parmsivan.kannan Grasshopper Points: 20 More actions · Answer 12

parmsivan.kannan

Grasshopper

Points: 20

January 28, 2020 at 2:16 pm

#3720922

Yeah... Agree...