none
Removing specific characters in the middle of a strings of assorted lengths RRS feed

  • Question

  • I have to remove extra zeros after a dash in the middle of a string. The issue is that there are multiple dashes in the string, but thankfully its always after the last one. I don't want to remove all of the following information, just the zeroes that come before the useful information. The values are in the following formats:

    Bad -> Good

    1-AB-CD-001 -> 1-AB-CD-1

    ABC-DE-01 -> ABC-DE-1

    ABC-DEF-010 -> ABC-DEF-10

    1-A-BCD-101 is good as is

    I figured out how to find the right point, but the closest I've come was to delete all the numbers, I can't seem to figure out how to just remove those pesky leading zeros.

    UPDATE [table]
    SET [table].[field] = Left([table].[field],InStr([table].[field],"-0")+0)
    WHERE ((([table].[field]) Like "*-0*" Or ([table].[field]) Like "*-00*"));
    Friday, June 8, 2018 6:48 PM

Answers

  • Hi Brandon,

    Make sure you have a backup copy before trying the following:

    UPDATE TableName
    SET FieldName = Left([FieldName],InStrRev([FieldName],"-")) & Val(Mid([FieldName],InStrRev([FieldName],"-")+1))
    WHERE InStr([FieldName],"-")>0

    (untested)

    Hope it helps...

    • Proposed as answer by Terry Xu - MSFT Monday, June 11, 2018 2:32 AM
    • Marked as answer by Brandon HC Monday, June 11, 2018 11:15 AM
    Friday, June 8, 2018 7:22 PM

All replies

  • I figured out most of it, but I can't seem to figure out how to get the -00 to update at the same time. I know it must be simple, but I don't get it. Right now I got it to see everything I want to update, but it will only update the one that is -0. I know I can just run it twice, but is there a quick code that will just do it all for me?

    UPDATE [table]
    SET [table].[field] = REPLACE ([table].[field],"-0", "-")
    WHERE ((([table].[field]) Like "*-0*" Or ([table].[field]) Like "*-00*"));

    Friday, June 8, 2018 7:17 PM
  • Hi Brandon,

    Make sure you have a backup copy before trying the following:

    UPDATE TableName
    SET FieldName = Left([FieldName],InStrRev([FieldName],"-")) & Val(Mid([FieldName],InStrRev([FieldName],"-")+1))
    WHERE InStr([FieldName],"-")>0

    (untested)

    Hope it helps...

    • Proposed as answer by Terry Xu - MSFT Monday, June 11, 2018 2:32 AM
    • Marked as answer by Brandon HC Monday, June 11, 2018 11:15 AM
    Friday, June 8, 2018 7:22 PM
  • UPDATE [table]
    SET [table].[field] = REPLACE ([table].[field],"-0", "-")
    WHERE ((([table].[field]) Like "*-0*" Or ([table].[field]) Like "*-00*"));

    Hi Brandon,

    You could try to do the Replace-function two times in a row.

    In case of "-00" the first replace will it convert to "-0", the second time to "-" 

    UPDATE [table] 
     SET [table].[field] = Replace (REPLACE ([table].[field],"-0", "-"),"-0", "-")
     WHERE ((([table].[field]) Like "*-0*" Or ([table].[field]) Like "*-00*"));

    Imb.

    Friday, June 8, 2018 7:43 PM
  • The following expression should do the trick:

    Left(TheString,InstrRev(TheString,"-")) & Val(Mid(TheString,InstrRev(TheString,"-")+1))

    You can see how it works with your examples in the immediate window:

    TheString = "1-AB-CD-001"
    ? Left(TheString,InstrRev(TheString,"-")) & Val(Mid(TheString,InstrRev(TheString,"-")+1))
    1-AB-CD-1
    TheString = "ABC-DE-01"
    ? Left(TheString,InstrRev(TheString,"-")) & Val(Mid(TheString,InstrRev(TheString,"-")+1))
    ABC-DE-1
    TheString = "ABC-DEF-010"
    ? Left(TheString,InstrRev(TheString,"-")) & Val(Mid(TheString,InstrRev(TheString,"-")+1))
    ABC-DEF-10
    TheString = "1-A-BCD-101"
    ? Left(TheString,InstrRev(TheString,"-")) & Val(Mid(TheString,InstrRev(TheString,"-")+1))
    1-A-BCD-101

    PS: I've just noticed that theDBguy had already come up with exactly the same solution!


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Saturday, June 9, 2018 4:27 PM Postscript added
    Saturday, June 9, 2018 4:21 PM