[RESOLVED] Uk postcodes poor structure

[nabbster]

Hi guys,

I am wondering how i address this problem i am trying to search a table of uk postcodes the problem is simply the table contains part postcodes and i am trying to search with full postcode I.e

Table contents:

IG1
IG10
N1
SE1
SE10
SE16EJ
SE16FJ

but the postcode being typed in is complete

Typed by user:

IG1 3GH
IG10 3PG
N1 2NN
SE1 2BN
SE103JC

These are just examples I am awear of the LIKE sql but this works in reverse to what i am trying to achieve........ I think.

Any direction would be great.

[techgnome]

the LIKE can work any side of the equation, you just have to structure it right.
Assumptions: @postalCode is the parameter passed in sPostalCode is the field in the database....

WHERE @postalCode LIKE (sPostalCode + '%') --- for a starts with
WHERE @postalCode LIKE ('%' + sPostalCode + '%') --- for a contains
WHERE @postalCode LIKE ('%' + sPostalCode) --- for a ends with

-tg

[nabbster]

Hi,
Thanks for the reply forgive my ignorance but that code looks almost the same as mine and seems like it is doing exacly what my sql statement does. The "@" symbol is the main difference is this correct because when i added it to my string i got an error. this is my SQL string.

StrSql = "SELECT * FROM Postcodes WHERE Postcode LIKE '%" + ActiveSheet.Range("E19").Value + "%'"
    rs.Open StrSql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

Please could you clarify?

[techgnome]

Almost.... need to reverse it from what you had.
Try this:

StrSql = "SELECT * FROM Postcodes WHERE '" & ActiveSheet.Range("E19").Value & "' LIKE(Postcode + '%')"

You didn't specify the database or the language platform, so I assumed VB and SQL Server - the @ is used to denote a parmeter in SQL Server...

-tg

[nabbster]

Sorry i was very silly there its access i am using from a vb app - Thanks very much for this some thing else I have learned