Post reply

PATINDEX

  • November 20, 2006 at 5:59 pm

    #91941

    I have a field that stores files imported called ImportFile.  I need to move the date into a numeric field called ImportDate.  The date is between the 2nd and 3rd underscore.  I messed with PATINDEX but was unable to figure it out.   I believe that is the right way to do this.  If there are any PATINDEX experts I'd appreciate the guidance.  Thanks.

    Other_pg3of7_11202006_Ndc_N.csv

    Other_pg4of7_11202006_Ndc_N.csv

    Other_pg5of7_11202006_Ndc_N.csv

    Other_pg6of7_11202006_Ndc_N.csv

    Other_pg7of7_11202006_Ndc_N.csv

    Other_pg10of18_11192006_Tan1_1.csv

    Other_pg10of23_11192006_Vit7_7.csv

    Other_pg11of18_11192006_Tan1_1.csv

    Other_pg11of23_11192006_Vit7_7.csv

    Other_pg12of18_11192006_Tan1_1.csv

    Other_pg12of23_11192006_Vit7_7.csv

  • November 20, 2006 at 6:59 pm

    #673110

    try something like this:

    declare

    @tststr varchar(128)

    set

    @tststr = 'Other_pg3of7_11202006_Ndc_N.csv'

    select

    patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', @tststr),

    substring(@tststr, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', @tststr), 8)

  • November 20, 2006 at 7:21 pm

    #673114

    That works great  ... I just realized they send dates weird also.  I can get:

    11 - 9 instead of 1109

    Other_pg12of23_1192006_Vit7_7.csv

    or

    Jan 1 2007

    They send:

    112007

    Wish they sent

    01012007

     

  • November 20, 2006 at 7:42 pm

    #673115

    How about something like this then:

    declare

    @tststr varchar(128)

    set

    @tststr = 'Other_pg12of23_112007_Vit7_7.csv' --'Other_pg12of23_1192006_Vit7_7.csv' --'Other_pg3of7_11202006_Ndc_N.csv'

    select

    case when patindex('%_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]_%', @tststr) > 0

    then substring(@tststr, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', @tststr), 8)

    when patindex('%_[0-9][0-9][0-9][0-9][0-9][0-9][0-9]_%', @tststr) > 0

    then substring(@tststr, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', @tststr), 7)

    when patindex('%_[0-9][0-9][0-9][0-9][0-9][0-9]_%', @tststr) > 0

    then substring(@tststr, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9]%', @tststr), 6)

    end

  • November 20, 2006 at 7:56 pm

    #673117

    That works perfect.  Interesting how you did it.  Thank you!!!

  • November 20, 2006 at 8:06 pm

    #673118

    You are welcome.

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

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