This removes all zeroes, I need the trailing zero removed

  • UPDATE tbl_Assembly_Production

    SET WorkOrder = REPLACE(RTRIM(REPLACE(WorkOrder, '0', ' ')), ' ', '5')

  • I wasn't sure if the trailing 0 would actually be the last digit in the string or not. If it is then this can be simplified a lot. Just use the reverse() function.

    DECLARE @test VARCHAR(50)

    SET @test = '01005060699010'

    SELECT REPLACE(REVERSE(STUFF(REVERSE(@test),CHARINDEX('0',REVERSE(@test)),1,'*')),'*','')

  • If you just want to get rid of the trailing zeroes from a number, you can reverse it, cast that as an Int, cast back to varchar and re-reverse.

    Is that what you're trying to do? (This thread makes it look like I'm coming into the middle of a conversation that I don't know the beginning of.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • It looks to me as if he is trying to replace the zeroes with a space and then trim off the space and then replace the space with a 5. Is that correct?

  • gboyer (9/4/2009)


    It looks to me as if he is trying to replace the zeroes with a space and then trim off the space and then replace the space with a 5. Is that correct?

    If that's the case, I'd do it like this:

    declare @String varchar(100);

    select @String = '12340000';

    select reverse(stuff(

    reverse(@String),

    1,

    patindex('%[1-9]%', reverse(@String))-1,

    replicate('5', patindex('%[1-9]%', reverse(@String))-1)));

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thanks for replying, i'm just now getting the chance to reply. I have workorder numbers that look like this: 'M205100'. I removed the M with a

    UPDATE tbl_Name

    SET workOrder = REPLACE(WORKORDER, 'M', ' ')

    But when I do the code in the first post, I remove all zeros and I only want to remove the trailing zero. I'll try the other samples on Tuesday.

  • Do you want this to read 20510 or 2051 ??

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Bob,

    I want it to read 20510, it should be five characters.

  • It sounds to me that it's not so much that you want to remove the trailing zeros as it is that you only want the first 5 characters. So, try this:

    UPDATE tbl_Assembly_Production

    SET WorkOrder = LEFT(WorkOrder, 5)

    and, for good measure, you can combine it with the code to remove the "M":

    UPDATE tbl_Assembly_Production

    SET WorkOrder = LEFT(REPLACE(WorkOrder, 'M', ''), 5)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • There are any number of ways to convert M205100 to 20510. You could use LEFT() and RIGHT() nested. You could STUFF() the first and last characters to blanks. You could use SUBSTRING() from position 2 for a length = len([col]) - 2. But we want to be sure that we don't oversimplify.

    Wayne's solution for example has to make several assumptions: (1) The only letter that will appear in the string is "M", (2) the "M" will appear at the front of the string, and (3) only the leftmost 5 numbers are desired. Are those assumptions correct? Giving only a single example really isn't enough. Will the answer ever have to deal with any strings like these?

    M20510 -- only one trailing zero

    M2051000 -- three trailing zeroes

    M20051 -- no trailing zeroes

    M2051 -- shorter numeric string, no trailing zeroes

    M200005100 -- longer numeric string

    M2000051 -- longer numeric string, no trailing zeroes

    X205100 -- starts with 'X', or any other letter thank 'M'

    LM205100 -- starts with two letters, not just one

    1205100 -- starts with a number, not a letter

    If so, what should the result be in each case?

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Wayne and Bob,

    Thanks. I'm going to try that Tuesday.

  • WayneS,

    Thanks a lot. I used the second statement and I got the results I wanted.

    Thanks again.

  • Thanks for posting back your final solution, kabaari.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

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