finding duplicate entries in an inventory file

[Greg]

I am working on an inventory file that has about 63,000 entries.

Over time many dupilcate entries have popped up in this file.

Often this is caused by the presence or absence of dashes in a field called "manuf_nbr" or leading zeros.

I was able to create a dynamic field called "myNum" that removed the dashes by using this function:

replace(manuf_nbr, "-", "")

I then grouped on "myNum", added a count field, which basically produced a list of all the "myNum".

I filtered this list to display only the myNums with counts greater than 1 to identify the potential duplicates.

This process identified 1200 potential duplicates.

My question is, which function or process would I use to strip out leading zeros?

[Aaron]

To strip out leading zeros: LTRIM(MANUF_NBR, '0')
To strip out trailing zeros: RTRIM(MANUF_NBR, '0')
To strip out both leading and trailing zeros: TRIM(MANUF_NBR, '0')

You can also use the TRANSLATE function to remove more than one character from the whole string. For example, if you want to strip out both hyphens ('-') and slashes ('/'), use TRANSLATE(MANUF_NBR, '-/', '')