Extract date from String

  • SQL Server

    Old Hand

    Points: 349

    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: 349

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

  • scdecade

    SSChasing Mays

    Points: 654

    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 weeks, 1 day ago by  scdecade. Reason: now looks for reverse('_files')

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

  • crow1969

    SSCrazy

    Points: 2970

    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: 6408

    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

  • scdecade

    SSChasing Mays

    Points: 654

    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: 995650

     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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 442235

    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: 995650

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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

    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: 442235

    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

    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

     

  • Jeff Moden

    SSC Guru

    Points: 995650

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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

    Yeah... Agree...

     

     

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

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