Kirix Support Forums

finding duplicate entries in an inventory file

Please post any help questions, requests or other feedback here.

finding duplicate entries in an inventory file

Postby Greg on Wed Jul 25, 2007 4:44 am

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?
Registered User
Posts: 4
Joined: Mon Jul 16, 2007 4:19 am

Re: finding duplicate entries in an inventory file

Postby Aaron on Wed Jul 25, 2007 10:30 am

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, '-/', '')
Aaron Williams
Kirix Support Team
User avatar
Kirix Support Team
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Return to Strata Help & Feedback