I need to do a look up on a table based on each word in a string

Question

I have a table of countries and their abbreviations with a column called code with the abbreviations and a column called name that contains the country names.

I need to iterate through a foreign address and look up each word until I find a match in the table (i.e. the country) and retrieve the abbreviation for a case statement.

This won't be done on all records, only certain ones that would be larger than a field of a 60 chars in a file I'm building.

So What I need to do is something to affect of:

SELECT 
CASE WHEN address2 & foreign_address > 60
THEN split and iterate through '12345 MY SUPER LONG ADDRESS IN THE PHILIPPINES' and look up
each string until PHILIPPINES is matched in the country_codes table and 'PH' is returned
END

This is the best way I can think to handle this situation short of truncating the address which I don't want to do for obvious reasons. This also needs to be dynamic based on different addresses and countries.

My biggest challenge at this point is breaking up the string and doing a look up on each string fragment.

Answer 1

SELECT Addresses.foreign_address, Countries.Code
FROM Addresses, Countries
WHERE LEN(foreign_address) > 60
AND foreign_address LIKE '%' Countries.Name '%'

[Code] will contain the abbreviated code of the country that was matched.

Here's the relevant SQLFiddle (and code below in full):

CREATE TABLE Countries (Name varchar(128), Code varchar(2));

CREATE TABLE Addresses (foreign_address varchar(512));

INSERT INTO Countries(Name,Code) VALUES('PHILIPPINES', 'PH'); 

INSERT INTO Addresses(foreign_address)
VALUES('12345 MY SUPER LONG ADDRESS IN THE PHILIPPINES UNTIL PHILIPPINES IS MATCHED AND PH IS RETURNED');

SELECT Addresses.foreign_address, Countries.Code
FROM Addresses, Countries
WHERE LEN(foreign_address) > 60
AND foreign_address LIKE '%' + Countries.Name + '%'

Answer 2

Something like this, may be?

    select l.abbr, a.id 
    from 
     lookup_table l, address_table a
    where 
     charindex(l.country_name, a.address2 + a.foreign_address) > 0 
     and len( a.address2 + a.foreign_address) > 60

Not tested.

Edited for the SQL Server 2005 string concatenation operator.

Answer 3

You can use LIKE instead of breaking up by word:

declare @address2 varchar(128)
declare @foreign_address varchar(128)

set @address2 = '12345 MY SUPER LONG ADDRESS IN THE PHILIPPINES'
set @foreign_address = '123456789012345678901234567890123456789012345678901234567890'

SELECT 
CASE 
    WHEN len(@address2 + @foreign_address) > 60 
    AND (' ' + @address2 + ' ' + @foreign_address + ' ') like '% PHILIPPINES %' 
THEN 'PH' 
ELSE NULL 
END 

Answer 4

I am not advocating this style of programming in SQL Server, but this is how I would do what you asked to do. First, tokenize the string into single words in a table variable. Then opena a cursor on the table variable and loop through the words, calling break if we find a result from dbo.countries. Please note, loops are very inefficient in SQL Server. The UDF table function came from here: How to split a string in T-SQL?

-- Create the UDF
CREATE FUNCTION Splitfn(@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (items varchar(8000))       
as       
begin       
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return      

end

go
-- Create the dbo.countries table so we can test our code later
create table dbo.countries (code char(2), name varchar(100))
go
-- Insert one record in dbo.countries so we can test our code later
insert into dbo.countries (code, name)
select 'PH', 'PHILIPPINES'
go
-- for one @String input, this is what I would do
declare @String varchar(1000) = '12345 MY SUPER LONG ADDRESS IN THE PHILIPPINES'
declare @CountryCode char(2) = ''
declare @done bit = 0x0
declare @word varchar(1000)

declare @words table (word varchar(250) primary key)

-- Break apart your @String into a table of records, only returning the DISTINCT values.  
-- Join on the domain list so we can only process the ones that will return data in the CURSOR (eliminating excess looping)
insert into @words (word)
--
select  distinct items as word
from    dbo.Splitfn(@String, ' ') s
join    dbo.countries c 
on      lower(c.name) = lower(s.items)

declare word_cursor CURSOR for
select  word
from    @words w

open word_cursor

fetch next from word_cursor into @word 

while @@FETCH_STATUS = 0
begin
    select      @CountryCode = code
    from        dbo.countries
    where       name = @word

    if @@trancount > 0
    begin
        break
    end

    fetch next from word_cursor into @word 
end

-- clean up the cursor
close word_cursor
deallocate word_cursor

-- return the found CountryCode
select @CountryCode