MySQL "Field Like Array" Functionality

Question

I have a query like:

SELECT ID FROM SomeTable WHERE ZipCode IN (SELECT Zip FROM SomeOtherTable)

This works as expected, however I need have a LIKE command. Some US zip codes are XXXXX-XXXX. I need the statement to match the first 5 characters only, even if it has more.

My PHP zip code check is:

preg_match("/^([0-9]{5})(-[0-9]{4})?$/i",$zip_code)

to show the format of how the zips are checked to be stored.

I have read about there being no IN LIKE command and to use REGEXP, but I am not sure how to use REGEXP with a select statement.

How would I go about changing this MySQL statement to match zips by the first 5 characters only?

dj_segfault · Accepted Answer · 2011-09-20 13:15:10Z

2

You can change the query to:

SELECT ID FROM SomeTable WHERE SUBSTR(ZipCode,0,5) IN (SELECT SUBSTR(Zip,0,5) FROM SomeOtherTable)

By the way, if SomeOtherTable has more than one record per zip code, you may be able to speed things up by adding DISTINCT to the inner query so it produces just a unique list of zip codes. See the SELECT documentation.

answered Sep 20, 2011 at 13:15

dj_segfault

12.5k4 gold badges33 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fanfavorite Over a year ago

Looks like it should work, but the substr and substring don't return anything for some reason. It returns a row, but it's blank. I tried to make it easy SELECT SUBSTR(Zip,0,5) FROM SomeOtherTable and it returns a bunch of blank results. I tried changing Zip to something text value like "zipcode" and still nothing.

Aaron W. Over a year ago

SUBSTR isn't zero based. Try SUBSTR(ZipCode,1,5)

mikey · Accepted Answer · 2011-09-20 13:19:36Z

0

LEFT or RIGHT as used at the end of this tutorial - http://www.mysqltutorial.org/sql-like-mysql.aspx

answered Sep 20, 2011 at 13:19

mikey

2,0921 gold badge13 silver badges6 bronze badges

1 Comment

fanfavorite Over a year ago

Can't figure out how to implement this. It's straight forward with 1 value, but not sure how to do it with a select statement.

Collectives™ on Stack Overflow

MySQL "Field Like Array" Functionality

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related