Post reply

Case and replace

  • August 30, 2017 at 4:05 am

    #328226

    Hi,

    I have got a number of records that begin with 044, I need to replace that with 0. I am using this replace(cli,'044','0'), however some records are 044123044 and so it replaces the end of the record with 0 also.  How can I get it to just replace the first three characters.

    I've tried using a left function but that just updates all the records to 0.

    Thanks,

    Gwyn

  • August 30, 2017 at 4:24 am

    #1957399

    Something like this will do

    select '044123044' as OrginialValue, STUFF('044123044', 1,3,'0') as AdjustedValue

  • August 30, 2017 at 4:38 am

    #1957401

    twin.devil - Wednesday, August 30, 2017 4:24 AM

    Something like this will do

    select '044123044' as OrginialValue, STUFF('044123044', 1,3,'0') as AdjustedValue

    with a WHERE clause, of course.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • August 30, 2017 at 4:43 am

    #1957402

    ChrisM@Work - Wednesday, August 30, 2017 4:38 AM

    twin.devil - Wednesday, August 30, 2017 4:24 AM

    Something like this will do

    select '044123044' as OrginialValue, STUFF('044123044', 1,3,'0') as AdjustedValue

    with a WHERE clause, of course.

    Of course If there would a table, not for static values

  • August 30, 2017 at 4:45 am

    #1957403

    Brilliant - I've never heard of STUFF before!

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

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