CODEWITHSUNDEEP
sqlsql-serversql-like
Heislegend31
Heislegend31

Reputation: 41

SQL look up abbreviation value

So i have declared a table: 

  DECLARE @VTable TABLE (ColtoAdj VARCHAR(50),HeadlineDescription 
  VARCHAR(50),HeadlineDescriptionGroup VARCHAR(50));

  INSERT INTO @VTable VALUES ('Neopla','Neoplasia','Cancer');
  INSERT INTO @VTable VALUES ('Metasta','Metastases','Cancer');
  INSERT INTO @VTable VALUES ('CES','CES','Cauda Equina');
  INSERT INTO @VTable VALUES ('CES-I','CES-I','Cauda Equina');

which is then used in an Outter apply on the main table to find the headlinedescription and headlinedescriptiongroup. 

  OUTER APPLY (
  SELECT TOP 1
  vt.ColtoAdj,
  Vt.HeadlineDescription,
  Vt.HeadlineDescriptionGroup
  FROM @VTable AS vt
  WHERE cmd.Headline LIKE ('%' + vt.ColtoAdj + '%')

Which for most of my data it works fine as alot of the vt.ColtoAdj lookup words are unique such as have a hyphon etc. so are only pulled. The problem is the CES value. This could be anywhere in a 255 character headline. So its currently picking up things like "BraCES" and adding it incorrectly. I tried to insert 2 values for it 'CES' or 'CES' to just get the abbreviation but this still grabs words ending in it or starting. Is there anyway for this value in the table i can get it to find just the 3 letters by themselves, but also search for full words for the other ones?

It may be a case of no, simply because its a like function and with a word lookup you cant always guarantee 100% accuracy, but thought i would check.

Upvotes: 1

Views: 106

Answers (2)

George Menoutis
George Menoutis

Reputation: 7240

I can only think of a per-case solution:

WHERE 
(
    (cmd.Headline LIKE ('%' + vt.ColtoAdj + '%') and vt.ColtoAdj<>'CES')
    OR
    (cmd.Headline='CES' and vt.ColtoAdj='CES')
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If you are looking for word boundaries and words are bounded by a space, you can use:

WHERE ' ' + cmd.Headline + ' ' LIKE '% ' + vt.ColtoAdj + ' %'

Upvotes: 2

Related Questions