Find variable string inside another string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find variable string inside another string

Author

Topic

obiron
Starting Member

23 Posts

Posted - 2009-03-18 : 11:08:35

Hi guys, this one currently has me stumped :(

I have a table with addresses and another table with country names and a country code.

I want to look at the addresses and report all the potential country codes that could be in that address.

E.g. If the Address is "12 Iceland Way, Perth, New South Wales, Australia"

I would want to match that to Iceland, Wales and Australia.

Alternatively, I want to list all the country codes and the addresses that contain them.

I have no text extenders on the tables and I have no unique key between the tables.

.

Table Structures:
Address Table:
RecordID int identifier91,1)
Address_1 varchar(35)
Address_2 varchar(35)
Address_3 varchar(35)
Address_4 varchar(35)
Address_5 varchar(35)

Countries Table:
CountryCode varchar(8)
CountryName varcahr(20)

I would really like to avoid cursors as the tables are 850 x 60K rows

TIA

Obiron

obiron
Starting Member

23 Posts

Posted - 2009-03-18 : 12:14:03

OK,

BFI Method (Brute force and Ignorance)


SELECT
       C.ID,
       C.Name,
       X.UniqueID,
       X.Address 
FROM
       tbl_Country C
FULL OUTER JOIN
(
    SELECT	A.UniqueID,
                A.Address
    FROM	       
                tbl_Addresses A 
) X on 1=1
WHERE
        charindex(' '+C.name+' ',x.address) <> 0
ORDER BY
        x.UniqueID

It can take a long time becuase there is no optimisation on it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 13:07:16

may be this


SELECT RecordID,AddVal,CountryName
FROM
(
SELECT a.RecordID,a.AddVal
FROM Address a
UNPIVOT (AddVal FOR Address IN ([Address_1],[Address_2],[Address_3],[Address_4],[Address_5]))u
)m
INNER JOIN Countries c
ON m.AddVal LIKE '% ' + c.CountryName + ' %'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources