Passing a list through a LIKE operator in SQL (SSMS)

Question

I have a table of company info (name, HQ country, etc) and a list of country names.

I want to see which companies have a country name in their company name, but ARE NOT headquartered in that country.

I have a simple SQL query to do it one at a time, but I want to write a query that passes all countries through.

Right now my where clause effectively looks like

WHERE company LIKE '%CANADA%'
  AND hq_country_full != 'CANADA'

I tried using a subquery as well but could not figure this one out. I'm thinking I probably need to use a local variable but not sure how to set it up. Any help would be appreciated!

Ah, your looking for VITALY SHOES that is not in Italy and FRANCESCO'S STORE that is not in France. Good luck :-) — Thorsten Kettner
– Thorsten Kettner, Commented Aug 13, 2018 at 20:53

Joe Farrell · Accepted Answer · 2018-08-13 20:35:30Z

2

Something like this, perhaps?

declare @Company table (company varchar(128), hq_country_full varchar(128));
insert @Company values
    ('Canada Company', 'Canada'),
    ('Non-Canada Company', 'Mexico'),
    ('Mexico Company', 'Mexico'),
    ('Non-Mexico Company', 'Canada');

declare @Country table (country varchar(128));
insert @Country values
    ('Canada'),
    ('Mexico');

select
    Company.*
from
    @Company Company
where
    exists (select 1 from @Country Country where 
        Company.hq_country_full != Country.country and
        Company.company like '%' + Country.country + '%');

Results:

company              hq_country_full
Non-Canada Company   Mexico
Non-Mexico Company   Canada

I've opted for a semi-join (exists) rather than a left join because it's theoretically possible that a company could satisfy the criterion you described for multiple countries, and I supposed that you'd only want the country to appear in the result set once in such a case. If you want multiple results in that case, use a left join instead.

answered Aug 13, 2018 at 20:35

Joe Farrell

3,5421 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ethan E Over a year ago

hey this looks promising! I'm unfamiliar with this type of sql but i'm assuming you can run a select for the insert so that I don't have to manually type out 80 million+ rows? I think I found my solution but I'm interested in this approach, as mine isn't 100% airtight

Joe Farrell Over a year ago

@EthanE - The purpose of my DECLARE and INSERT statements is just to set up some sample data since I didn't have your original tables to work with. You wouldn't need them at all, assuming this data is already in your database somewhere. Just use something like my SELECT, with @Country and @Company replaced with your actual table names.

LukStorms · Accepted Answer · 2018-08-13 21:40:01Z

1

You could put that list with country names in a table. That can even be a #temporary table or a @table variable.

After that you can just join the company info table to the country names table with a LIKE.

-- Using a table variable for the example
declare @countrynames table (name varchar(80) primary key);
insert into @countrynames (name) values
 ('CANADA')
,('BELGIUM')
,('RUSSIA')
,('USA');


SELECT 
 comp.company, 
 comp.hq_country_full
 ctry.name as countryName
FROM companyinfo comp 
JOIN @countrynames as ctry
  ON comp.company LIKE '%'+ctry.name+'%'
WHERE comp.hq_country_full != ctry.name

edited Aug 13, 2018 at 21:40

answered Aug 13, 2018 at 20:44

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Comments

Dave.Gugg · Accepted Answer · 2018-08-13 20:32:05Z

0

Seems like since you have a hq_country_full column, you can use that in the LIKE clause:

WHERE company NOT LIKE '%' + hq_country_full + '%'

answered Aug 13, 2018 at 20:32

Dave.Gugg

6,8013 gold badges26 silver badges45 bronze badges

Collectives™ on Stack Overflow

Passing a list through a LIKE operator in SQL (SSMS)

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related