CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2matchpartial
Adam Haycock
Adam Haycock

Reputation: 197

SQL UK postcode matching query

I have two tables, one with partial UK postcodes and one with full UK postcodes. I need to return those rows within the full postcodes which match the partial postcodes. Please can you advise on the code to do this as I am confusing myself.

Here's an example of the tables:

    Table1

    Postcode
    AB10 1


    Table2

    Postcode    Title FName  SName
    AB10 1NN    Ms    Davina Gabriel
    AB11 5BY    Mr    James  Mclean
    AB11 5DL    Mrs   Janet  Maccallum
    AB11 5DP    Mr    Mick   Milne
    AB11 5DY    Mr    Trevor Mcwhinnie
    AB10 1GJ    Mrs   Ruth   Smith

In the above example I am just looking for Ms Davina Gabriel and Ruth Smith to be returned.

Upvotes: 0

Views: 744

Answers (2)

Sankar
Sankar

Reputation: 7107

Try this...

select a.* from table2 a, table1 b where a.postalcode like b.postalcode + '%'

Upvotes: 1

VoteyDisciple
VoteyDisciple

Reputation: 37803

How about...

SELECT *
FROM Table2
INNER JOIN Table1 ON (Table2.Postcode LIKE CONCAT(Table1.Postcode, '%'))

Upvotes: 1

Related Questions