Post reply

Replace and Substring

  • August 24, 2015 at 2:51 pm

    #299942

    Here's my substring script:

    I want dob to be replaced with value of Date.

    So, how can I use replace and substring together?

    select DOB,substring(DOB,5,4)+SUBSTRING(DOB,1,2)+SUBSTRING(DOB,3,2)as Date,ID

    from TblNAme

    WHERE ISDATE([DOB]) != 1

    and DOB is not null

    and DOB <>''

    Result:

    dOB Date ID

    0301198119810301503

  • August 24, 2015 at 3:28 pm

    #1822110

    UPATE TblNAme

    SET DOB = substring(DOB,5,4)+SUBSTRING(DOB,1,2)+SUBSTRING(DOB,3,2)

    WHERE ISDATE([DOB]) != 1

    and DOB is not null

    and DOB <>''

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

  • August 24, 2015 at 7:34 pm

    #1822125

    Just an added tip...

    This...

    and DOB is not null

    and DOB <>''

    ... is the same as this...

    and DOB > ''

    ... and can sometimes be a fair bit faster. It works because NULL cannot be compared and will, therefor, fail the single test.

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

  • August 25, 2015 at 7:44 am

    #1822202

    Also note that

    SUBSTRING(DOB,1,2)+SUBSTRING(DOB,3,2)

    is the same as

    SUBSTRING(DOB,1,4)

    I don't know if it will make a noticeable difference in performance, and I don't have the time to test it right now.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 25, 2015 at 8:02 am

    #1822205

    Thank you all.

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

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