Removing specific characters in the middle of a strings of assorted lengths

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*"));

Answer 1

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*"));

Answer 2

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

Answer 3

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.

Answer 4

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