Formatting post code string

Hi there,
I was wondering if someone could help me with a formatting problem for ASP/SQL?

Is it possible to format a variable string from a textbox?

I'll try and make it more clear what i want, sorry.

I have a page with a textbox and submit button for users to input a postcode, which then searches a database for matching or similar postcodes.

This is the query i am using:

<% Dim pcodequery1, pcodewhere1
pcodequery1="select * from tbl_basket"
pcodewhere1=" where basket_del_postcode like '%" & pcode & "%'"
pcodewhere1=pcodewhere1 & " order by basket_del_postcode desc"
pcodequery1=pcodequery1 & pcodewhere1 %>

I have already set the record sets etc. And the "pcode" variable is the name of the textbox variable, which i have set at the top of the page.

The query works fine for either exact matches or just typing a few letters.
The problem is that when the postcode has been entered it may or may not have a space in the middle, eg "hg2 7gk" or "hg27gk".
If i type a full postcode in and put a space in the middle where the original has no space i get told there is no match, and vice versa.

I'm sorry if this isn't very clear as this is my first post. If you need more from me just let me know.

Cheers loads,
Yaan out

ok, one question, do all postcodes have the same lengh?? if so the lets say the length of all postcodes are 6 characters, then simply limit your textbox to 6 characters. else....

it sounds like youre gonna need to do some string formatting... im not to sharp on this, but in Javascript, youre gonna need to SEARCH your STRING for "_" (a empty space) and erase that.

there may be a simpler solution, but im not sure.

Thanks for your reply, unfortunately i am working with a database which i have no control over how users input the postcodes.

The postcodes are not more than 8 characters long but could be as little as 6.

i don't think that taking the spaces out of the textbox input would work as i am having to search a database that is, unfortunately, not a set format.

Again, thanks for the help as it is greatly appreciated!

Yaan out

It is tough working with data that many different users have input.

Especially if you have no control over validation. If you change the form and create some javascript validation, that would solve future mis-typed postal codes (if these postal codes are all from the same region and therefore in the same format), but then you still have the ones in the database.

How many records are there? If there aren't many, you could go through and format each one correctly.

This would be the best approach, I think.

If there are lots, see if you can find common mistakes (like an extra space) and using an update query remove them.

Thanks for the feedback,
looks like i'm going to have to look at re-formatting the info in the database if possible.

Cheers for the help, i'll let you know if i find another solution.

Yaan out

That should work if you're using MS SQL (it's T-SQL compatible code) but I don't know about Access or MySQL etc. Replace 'stuff' with Replace(postcodestring," ","") in ASP.

Looks like it's going to have to be put up with until i can find a solution as it's not feasable to re-format the database data due to other dependancies.

I was wondering if there was a way of dissecting the search string and making the query first look for a whole match and then matches for just the first 2 or 3 characters?

Again, any suggestions are welcome.

Thanks for the help once again.

You could do this...

SELECT
myField
FROM
myTable
WHERE
otherField LIKE '%something%' OR otherField LIKE '%firstThreeChars%'


If you are using access & asp, just setup two variables for something and then use the left function to get the first three characters.

If you are using SQL Server, you could write a stored procedure to do it for you.

It's looking like the only way i could do it without re-formatting all the data would be if the server was running the full text search option shipped with MS SQL server 2000.
If it was running this then i should, in theory, be able to use the "CONTAINS" or "SIMILAR" queries.

I'll keep looking.

Cheers again,
Yaan

If you are running MS SQL server, then the T-SQL code I posted WILL work. There is no need to a full text search, as far as I can see.

Thanks for the "left" suggestion.

It now works, all i had to do was run the original query and then say that if there were no results, run a new query using a new variable: "pcode2=left(pcode, 3)"
I just told the new query to look for matches for this instead.

Thanks for the help, this is a fine forum!

Yaan out (again)