Query that returns two character records from MySQL table

Today I will show you how to get records of 2 characters that are mixed in with multiple character records. This particular example I experienced from my work. All 52 states in US each has 2 names: full name and abbreviation name. For example: California is a full name and CA is its abbreviation. However in Canada a state has only full name. For example: Manitoba.

The issue here is that when you have a state table in your database that contains the states of USA and Canada. I will show what query you should use to filter the list of only US States.

Your state table will look like this picture:

The query you will have to use to get only the US states is:

  1. $sql_statement = “Select * from mydb.state_table where CHAR_LENGTH(state_abbr)  = 2”;

And here is the list of states returned by the query above.