I am searching for a string like Cityname, State in mysql table where state name is 2 letter char.

Question

I am searching for a string like Cityname, State (e.g.Moline,IL) in mysql table. How can i make the query using regexp in mysql.

Answer 1

mySQL has a REGEXP operator that should do what you need:

11.4.2. Regular Expressions

Answer 2

You can try something like:

select foo from bar where foo REGEXP '[a-zA-Z ]+,\s*(AL|AK|AS|AZ|AR|CA|CO|CT|DE|DC|FM|FL|GA|GU|HI|ID|IL|IN|IA|KS|KY|LA|ME|MH|MD|MA|MI|MN|MS|MO|MT|NE|NV|NH|NJ|NM|NY|NC|ND|MP|OH|OK|OR|PW|PA|PR|RI|SC|SD|TN|TX|UT|VT|VI|VA|WA|WV|WI|WY)'

This expects valid city names to be made of upper/lower case letters A-Z, and optionally include one or more spaces. You can add other valid characters to the city name in the first set of [ ] brackets.

To see what regex features are supported by MySQL, see the POSIX ERE column on http://www.regular-expressions.info/refflavors.html

Regex Explanation

NODE                     EXPLANATION
--------------------------------------------------------------------------------
  [a-zA-Z ]+               any character of: 'a' to 'z', 'A' to 'Z',
                           ' ' (1 or more times (matching the most
                           amount possible))
--------------------------------------------------------------------------------
  ,                        ','
--------------------------------------------------------------------------------
  \s*                      whitespace (\n, \r, \t, \f, and " ") (0 or
                           more times (matching the most amount
                           possible))
--------------------------------------------------------------------------------
  (                        group and capture to \1:
--------------------------------------------------------------------------------
    AL|AK|AS|...             'AL' or 'AK' or 'AS' or ... 
                             (valid state abbreviations)
--------------------------------------------------------------------------------
  )                        end of \1