Post reply

How to Delete Right 3 Characters from a string

  • July 24, 2011 at 6:17 am

    #1357526

    Fair enuff. In the scenario I was using it I wanted to get rid of 3 letters that had been added to a file number e.g

    CaseCode: 1017856AVN.

    In database wanted to link it to the file number & file type were in separate columns i.e.

    Case number: 1017856 Case type: AVN

    So to get CaseCode in 1st DB to match Case number in 2nd DB Kingston's REPLACE worked fine.

  • July 24, 2011 at 6:22 am

    #1357527

    andrew.sugden (7/24/2011)

    Fair enuff. In the scenario I was using it I wanted to get rid of 3 letters that had been added to a file number e.g

    CaseCode: 1017856AVN.

    In database wanted to link it to the file number & file type were in separate columns i.e.

    Case number: 1017856 Case type: AVN

    So to get CaseCode in 1st DB to match Case number in 2nd DB Kingston's REPLACE worked fine.

    All we said is that it can delete the wrong data with replace. Left is clear as day. Whack the last 3 characters and most importantly, it cannot fail.

  • July 24, 2011 at 10:00 am

    #1357539

    I know. That's why said fair enuff.

  • July 24, 2011 at 10:13 pm

    #1357604

    Jeff Moden (7/22/2011)

    Sugsy (7/22/2011)

    Just for the record Kingston's suggestion works fine n'all

    Just for the record, it will also delete the wrong data if the right 3 occurs more than once in the string. Don't use it. 😉

    (Sorry, Kingston)

    This post is more than a year and a half old. When I saw it today, I was like "Why did i post that?". I will not give a similar reply today for sure. I hope I am more mature now and I am not a quick fix developer anymore who gives quick fixes which can prove nasty, thanks mainly to people like you in SQL Sever Central.

    And Jeff, I also remember you giving me firing for giving a similar sort of quick fix earlier. I didn't feel bad a single bit even that day. Keep them coming. I would love to be corrected.

    Once again, thanks Jeff.

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • July 25, 2011 at 4:58 am

    #1357682

    Kingston, you're the consumate gentleman and a dedicated programmer. I'd work with anytime. Thank you for the feedback. 🙂

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

  • July 25, 2011 at 9:04 am

    #1357841

    Ninja's_RGR'us (7/24/2011)

    All we said is that it can delete the wrong data with replace. Left is clear as day. Whack the last 3 characters and most importantly, it cannot fail.

    As long as there are a minimum of 3 characters in the field

    Director of Transmogrification Services

  • July 25, 2011 at 9:16 am

    #1357848

    Mad Myche (7/25/2011)

    Ninja's_RGR'us (7/24/2011)

    All we said is that it can delete the wrong data with replace. Left is clear as day. Whack the last 3 characters and most importantly, it cannot fail.

    As long as there are a minimum of 3 characters in the field

    Pick your poison, you always have to be careful with that type of manipulations. Here are the "fully correct" options.

    DECLARE @a TABLE (col VARCHAR(10))

    INSERT INTO @a (col) VALUES ('')

    INSERT INTO @a (col) VALUES (NULL)

    INSERT INTO @a (col) VALUES ('123')

    INSERT INTO @a (col) VALUES ('123123123')

    INSERT INTO @a (col) VALUES ('123456789')

    SELECT col

    , REPLACE(col, RIGHT(col, 3), '') AS [Replace = wrong output]

    , CASE WHEN col IS NOT NULL THEN ISNULL(STUFF(col, DATALENGTH(col) - 3 + 1, 3,''), '') ELSE NULL END AS Stuf

    , CASE WHEN DATALENGTH(col) >= 3 THEN LEFT(col, DATALENGTH(col) - 3) ELSE CASE WHEN col IS NULL THEN col ELSE '' END END AS Lft

    FROM @a

  • July 25, 2011 at 9:29 am

    #1357866

    I definitely agree with that. I have worked with too many people who design and finalize based on correct data being present, and have very little or no error prevention/handling.

    Looking at the original ages old post, I noticed that the data appears to be a 3 element asterisk delineated string. Depending on the needs, removal of the third element could be a better option; either in the data itself or at presentation/usage time

    Director of Transmogrification Services

  • July 25, 2011 at 9:36 am

    #1357880

    Mad Myche (7/25/2011)

    Looking at the original ages old post, I noticed that the data appears to be a 3 element asterisk delineated string. Depending on the needs, removal of the third element could be a better option; either in the data itself or at presentation/usage time

    I'd agree with that too. But we can only go with the specs given to us! :w00t:

  • July 25, 2011 at 9:50 am

    #1357901

    Agree with that as well I do.

    Often what they say they want is not really what they want, or is incomplete. I will always let the person giving the specs know if my view of the big picture strays from their possible tunnel vision. (and say "I told you so" when they come back months later asking me for what I already planned on)

    Director of Transmogrification Services

  • July 25, 2011 at 10:23 am

    #1357932

    Mad Myche (7/25/2011)

    Agree with that as well I do.

    Often what they say they want is not really what they want, or is incomplete. I will always let the person giving the specs know if my view of the big picture strays from their possible tunnel vision. (and say "I told you so" when they come back months later asking me for what I already planned on)

    Care to share specific stories?? :hehe:

  • March 25, 2014 at 9:15 am

    #1699935

    This helps! Thanks

Viewing 12 posts - 16 through 26 (of 26 total)

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