Duplicate Vendors After Cleaning Up Their Names

Medium
388.

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

ColumnType
vendor_idint
vendor_namevarchar
gstinchar

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.

Example

The rows must come back in the order shown.

VendorMaster

vendor_idvendor_namegstin
1Sharma Traders Pvt Ltd29ABCDE1234F1Z5
2Annapurna Foods27FGHIJ5678K1Z2
3 Sharma TradersNULL
4Deccan Distributors Ltd36KLMNO9012P1Z8
5Sharma Traders Ltd NULL
6Deccan Distributors36KLMNO9012P1Z8
7Gupta Agencies08QRSTU3456V1Z1

Output

clean_namevendor_countvendor_ids
Deccan Distributors24,6
Sharma Traders31,3,5
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
vendor_idint
vendor_namevarchar
gstinchar

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_idvendor_namegstin
1Sharma Traders Pvt Ltd29ABCDE1234F1Z5
2Annapurna Foods27FGHIJ5678K1Z2
3 Sharma TradersNULL
4Deccan Distributors Ltd36KLMNO9012P1Z8
5Sharma Traders Ltd NULL
6Deccan Distributors36KLMNO9012P1Z8
7Gupta Agencies08QRSTU3456V1Z1

Output

clean_namevendor_countvendor_ids
Deccan Distributors24,6
Sharma Traders31,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_name

Another 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 →