Duplicate Vendors After Cleaning Up Their Names
The vendor master was keyed in by different branches, so one distributor can appear several times: with stray spaces around the name, or with a Pvt Ltd or…
- Difficulty: Medium
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #388
Problem statement
The vendor master was keyed in by different branches, so one distributor can appear several times: with stray spaces around the name, or with a Pvt Ltd or Ltd suffix that another branch left out. The clean name of a vendor is its name with every Pvt Ltd and then every Ltd removed, and surrounding spaces trimmed.
Return every clean name shared by two or more vendors, with the columns clean_name, vendor_count and vendor_ids (the vendor ids in ascending order, joined with commas and no spaces, e.g. 3,7,12). Order the rows by clean_name.
Tables
Table: VendorMaster
| Column | Type |
|---|---|
| vendor_id | int |
| vendor_name | varchar |
| gstin | char |
Primary key: vendor_id.
One row per vendor record. Names always use the same capitalisation; only spacing and the company suffix vary. gstin is NULL where it was never captured.
Examples
Example 1
VendorMaster
| vendor_id | vendor_name | gstin |
|---|---|---|
| 1 | Sharma Traders Pvt Ltd | 29ABCDE1234F1Z5 |
| 2 | Annapurna Foods | 27FGHIJ5678K1Z2 |
| 3 | Sharma Traders | NULL |
| 4 | Deccan Distributors Ltd | 36KLMNO9012P1Z8 |
| 5 | Sharma Traders Ltd | NULL |
| 6 | Deccan Distributors | 36KLMNO9012P1Z8 |
| 7 | Gupta Agencies | 08QRSTU3456V1Z1 |
Output
| clean_name | vendor_count | vendor_ids |
|---|---|---|
| Deccan Distributors | 2 | 4,6 |
| Sharma Traders | 3 | 1,3,5 |
How to solve Duplicate Vendors After Cleaning Up Their Names
De-duplicating master data is mostly string work: define a normal form of the name and group on it. Here the normal form removes the company suffix and the surrounding whitespace. The order of the replacements matters — removing Ltd first would turn Sharma Traders Pvt Ltd into Sharma Traders Pvt — and TRIM comes last so that both leading spaces and a space left before a removed suffix disappear.
Once each record has its clean name, GROUP BY clean_name HAVING COUNT(*) >= 2 keeps the names with duplicates. GROUP_CONCAT lists the ids that collapse together; giving it ORDER BY vendor_id makes the list deterministic and SEPARATOR ',' removes the default's ambiguity. The GSTIN plays no part (it is often missing) but would be the natural next check for a reviewer.
The alternative trims first and strips a suffix only at the end of the name, using LIKE and LEFT with CHAR_LENGTH. On names where the suffix is a true suffix both forms agree; REPLACE would also remove a Ltd in the middle of a name, which this data never has. The cost is one pass and one grouping.
Reference solution (MySQL)
SELECT clean_name,
COUNT(*) AS vendor_count,
GROUP_CONCAT(vendor_id ORDER BY vendor_id SEPARATOR ',') AS vendor_ids
FROM (
SELECT vendor_id, TRIM(REPLACE(REPLACE(vendor_name, ' Pvt Ltd', ''), ' Ltd', '')) AS clean_name
FROM VendorMaster
) v
GROUP BY clean_name
HAVING COUNT(*) >= 2
ORDER BY clean_nameAnother way
WITH t AS (SELECT vendor_id, TRIM(vendor_name) AS nm FROM VendorMaster),
c AS (
SELECT vendor_id,
CASE WHEN nm LIKE '% Pvt Ltd' THEN LEFT(nm, CHAR_LENGTH(nm) - 8)
WHEN nm LIKE '% Ltd' THEN LEFT(nm, CHAR_LENGTH(nm) - 4)
ELSE nm END AS clean_name
FROM t
)
SELECT clean_name, COUNT(*) AS vendor_count, GROUP_CONCAT(vendor_id ORDER BY vendor_id SEPARATOR ',') AS vendor_ids
FROM c GROUP BY clean_name HAVING COUNT(*) > 1 ORDER BY clean_name← Slow-Moving SKUs Still Holding Stock · Warehouse Transfers Still in Transit →