postcode search

[moleboy]

Anyone,

I'm trying to search my database using the first part of the postcode to return records. The problem is when i put eh6, i get all eh6 plus eh61, eh64, etc. And when i use just eh I get no recods at all. I cant really use left due to the fact that some postcodes are only two characters like w1.

Can anyone show me any code to search for postcodes?

Cheers

Moley

 

[vbkris]

can i have ur query?

Known is handfull, Unknown is worldfull

 

[moleboy]

not very complicated i know

Select*From tblUsers Where post Like @postbox

 

[Genimuse]

The SQL LIKE command allows you to specify how things should match. It works slightly differently in different databases, but it should work the same in most.

The LIKE command allows you to specify wildcards. The "_" character represents a single wild character while the "%" character represents zero or more wild characters.

Examples:

Data consists of a table with a column alled PostCode that contains these entries:

    EH
    EH6
    EH61
    1EH

Here are some queries and their results:

SELECT * FROM MyTable WHERE PostCode LIKE 'EH'

    EH

SELECT * FROM MyTable WHERE PostCode LIKE 'EH_'

    EH6

SELECT * FROM MyTable WHERE PostCode LIKE 'EH%'

    EH
    EH6
    EH61

In your example you don't seem to be using any wildcard characters, unless you're sending them to a stored procedure (the @ sign clues me in) and I'm not seeing them.

Unfortunately I suspect your problem is a little more complex because the full postal code isn't just "EH6" or "EH61", but rather things like "EH6 CY2" -- that is, there's more to it than just those characters. In that case SQL won't help you much because

"LIKE 'EH%'"[/code[ is going to match all of them. You may need to further filter the results while moving through the records, probably using regular expressions to narrow down your results.

 

[Rilez]

sorry but wouldn't you just want to add a space to the end of it? ie instead of searching ... like 'eh6*' which I assume is what you're doing at the moment do like 'eh6 *'

Might be looking at this a little too simplistically but this is probabily the way I'd think of handling the problem

 

[Genimuse]

I just made up the space thing because I don't know what they actually look like. If there is a space like then then it would work fine indeed!