return row where 'x' CONTAINS 'y'

i have my data for zip codes stored as 99999-9999.

there is a search by zip code on my site.

i figure that most users will be using 99999 format.

how can i return rows where zip contains 99999?

 

What type of database are you using? Access, Oracle, SQL Server, MySQL, etc. The query would vary slightly depending on the database.

For MySQL, the query would be SELECT * FROM table WHERE ZipCode LIKE '%99999%'

The percent sign acts as sort of a wildcard so the above would search any where in the ZipCode field for 99999. If you wanted to only search the beginning of the field, then it would be LIKE '99999%'

 

Sounds like you store zip codes in two formats: a 5 digit format and a 4 digit format and you want to return rows where the 5 digit format is used.

Zip codes would be in integer so SELECT * FROM table WHERE ZipCode > 9999

Note: 9999 is a number and not a placeholder as in your example so the result would have rows where zip code is 10000 and higher

 

I would do it a little differently. Use:

SELECT * FROM table WHERE LEFT(ZipCode,5) = '99999'

You can place an index on the first 5 characters of the ZipCode field and you wont see any performance hit you would find with a Where '%%' query.

CREATE INDEX five_zip ON table (ZipCode (5));

 

i think this sort of search would work for you.. although it might not be the fastest

i believe i tried this before in sql server
SELECT *
FROM table
WHERE zip like '%searchstring%'

and the problem is that will only return rows where searchstring has values to the left and the right of it i.e. if you're searching 9999 it will return 89999-9999 but not 99998-8888 because there is nothing to the left of the leftmost 9

this version will check to see if the search string is anywhere within the zip field

SELECT *
FROM Table
WHERE zip LIKE 'searchstring'
OR zip LIKE '%searchstring'
OR zip LIKE 'searchstring%'
OR zip LIKE '%searchstring%'

 

substr(zipcode,1,5)