Fuzzy matching a List in SQL-CodePudding

Have a set of data that is riddled with duplicates. The company names are either written as their Workplace name, e.g. Amazon, or the legal name, e.g Amazon.com Inc. Both entries have information I need.

Issue with the name is I am running a subquery to generate the correct list of companies to search for, however the LIKE function only seems to work for a set list.

FROM CRM.organizations 
WHERE name LIKE (SELECT org_name FROM CRM.deals WHERE UUID IS NOT NULL AND status = 'won')```

The code above returns the following error: 'Error: Scalar subquery produced more than one element'

Trying to understand if there is a function that can help, or I will need to create a list manually with: 'companyAinc';'companyBllc';....

CodePudding user response：

Well, the LIKE operator doesn't support directly passing a list a values to match with, you can use the CROSS APPLY to map each value to fuzzy match in your statement.

You can refer to this example for the same to use multiple clauses with LIKE operator.

On the other hand you can also try using User-defined functions/routines, in which you can map all your the returned values with the LIKE and OR operators and return your required query as a string.

CodePudding user response：

FROM CRM.organizations WHERE name in (SELECT org_name FROM CRM.deals WHERE UUID IS NOT NULL AND status = 'won');

FROM CRM.organizations WHERE exists (SELECT 1 FROM CRM.deals WHERE UUID IS NOT NULL AND status = 'won' and organizations.name like deals.org_name );