SELECT or GROUP BY LIKE

• lanky_doodle

  SSCrazy

  Points: 2378

  More actions

  June 20, 2018 at 3:43 am

  #352044

  Hi,

  Is it possible to use (dynamic) LIKE in a SELECT/GROUP BY rather than WHERE. Take this sample:

  Corning Precision Lens Incorporated
  Corning Precision Materials Co., Ltd.
  Corning Research & Development Corporation
  Philips International B.V.
  Philips International B.V. - IP&S
  Philips International B.V.- IP&S

  Can I return just 2 rows:

  Corning
  Philips

  There are many, many more instances were the same company exist in our database with slight differences so CASE is not really possible. And because I don't know each company this exists with. I'm imaging something like (no pun intended :)) where the first word is used as the 'base'. So something using SUBSTRING.

  I'd rather clues than a solution 🙂

  Thanks

• ranitb

  Right there with Babe

  Points: 741

  More actions

  June 20, 2018 at 5:05 am

  #1994589

  Something like this ?

  ;with your_table_name(txt) as
(
select 'Corning Precision Lens Incorporated' union all
select 'Corning Precision Materials Co., Ltd.' union all
select 'Corning Research & Development Corporation' union all
select 'Philips International B.V.' union all
select 'Philips International B.V. - IP&S' union all
select 'Philips International B.V.- IP&S'
)
--// Above is test data - Below is the query
select left(txt,CHARINDEX(' ',txt)-1) comp
from your_table_name
group by left(txt,CHARINDEX(' ',txt)-1)

  O/p -
  Corning
Philips

• Jeff Moden

  SSC Guru

  Points: 1003867

  More actions

  June 20, 2018 at 5:45 am

  #1994595

  While ranitb's solution will work, you're going to run into problems doing the similarity check based only on the leading word or even a couple of words.  There will also be times where associations will not have any words to match at all.  My recommendation would be that you create a table of actual company names in your database along with a column of aliases that you'd like to match on.  It's not the easy way but it is the right way.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  June 20, 2018 at 8:55 am

  #1994657

  Jeff Moden - Wednesday, June 20, 2018 5:45 AM

  While ranitb's solution will work, you're going to run into problems doing the similarity check based only on the leading word or even a couple of words.  There will also be times where associations will not have any words to match at all.  My recommendation would be that you create a table of actual company names in your database along with a column of aliases that you'd like to match on.  It's not the easy way but it is the right way.

  + 1 googolplex to the googolplex power to that.   It would only be a matter of time before some piece of company name data came along and broke any code you might have that works.   Anything less than tying each and every company name to an "approved" abbreviation is doomed to eventual failure.   Yes, it's gonna be a PITA, but better some small PITA now than some major PITA when existing code breaks in a way that no one notices for a while, and then when it finally actually gets noticed, it's kind of "too late", so to speak.   You do NOT want to be the cause of that.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)