Best Practice For Lookup Tables In SQL

Question

I am fairly new to SQL so apologies if this questions sounds odd.

I keep coming across the issue of poor data. As an example London could be stored as LON, London UK, London England etc. Prior to using SQL I had lots of Excel lookup tables where in the first column I would have the original, and in the 2nd helper column I would have the corrected version. As an example:

Name             Name_1
London, UK       London
Lon              London
LON              London
London           London
London, England  London
LND              London

Is there a straightforward way of doing this in SQL? I am currently trying to create lookup tables and then use joins. This is becoming tricky as I do not always have corrections for every instance, so in most scenarios my lookup tables have fewer items than the tables I'm joining them to.

I've been teaching myself about stored procedures and I wondered if this could solve the problem. Trouble is my searching on the subject of lookup tables is coming up empty.

Any advice or pointers would be gratefully received, even if it is just to say that this cannot be done.

Thanks as always for you help and apologies for the long post.

Answer 1

You can join to the lookup table and preferrable use the value given there. If not found, use the original:

SELECT t1.FirstName, LookupField = ISNULL(t2.Name_1, t1.LookupField)
FROM People as t1
LEFT INNER JOIN TableLookupCities as t2 ON t1.LookupField = t2.Name

Make sure, that for each name there is at most one match in TableLookupCities, or else the join will produce multiple results. Create a unique index on TableLookupCities.Name:

CREATE UNIQUE (CLUSTERED) INDEX djgndkg ON TableLookupCities (Name) INCLUDE (Name_1)

Answer 2

You don't have to do any of the other things, just return the original if you don't have a translation for it.

SELECT
t1.FirstName,
t1.LookupField,
case when t2.Name_1 is null 
    then t1.lookupfield 
    else t2.name_1 end Name_1
FROM People as t1
LEFT INNER JOIN TableLookupCities as t2
ON t1.LookupField = t2.Name