Location name inconsistency in database

Question

I have a column of state names which has a mixture of abbreviations and full names of each of the states. I need to run a query in a way where I can standardize the format. Either "full names" or just the abbreviations.

Probably a good task for Regex. Check out this post: https://stackoverflow.com/questions/21587805/trying-to-come-up-with-regex-for-us-states-abbreviation-and-full-name-mixed-all — Jacob H, Oct 23 '17 at 20:01
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? Postgres? Oracle? "_SQL_" is just a query language, not the name of a specific database product. — , Oct 23 '17 at 20:23
Add some sample table data and the expected result. (As formatted text, not images.) — jarlh, Oct 23 '17 at 20:25

kjmerf · Answer 1 · 2017-10-23T20:38:52.787

0

I would suggest building a temp table, like this:

State, Abbreviation
Alabama, AL
Alaska, AK
Arizona, AZ

Then join the temp table to your table t:

SELECT temp.state
FROM t
INNER JOIN temp ON t.state IN (temp.state, temp.abbreviation)

edited Oct 23 '17 at 20:38

answered Oct 23 '17 at 20:06

kjmerf

4,275
3
21
29

Thanks a lot. this method is very useful. The only problem is there are many states. And of various countries. Is there any way I can tackle this issue? – user8821126 Oct 23 '17 at 20:16
Skip the UNION, do `... INNER JOIN temp ON t.state IN (temp.state, temp.abbreviation)` instead. – jarlh Oct 23 '17 at 20:24
@jarlh. What do you think of this post: https://stackoverflow.com/questions/5901791/is-having-an-or-in-an-inner-join-condition-a-bad-idea – kjmerf Oct 23 '17 at 20:27
@kbball, probably interesting for MS SQL Server users. But we have no dbms here, and no table or index definitions. – jarlh Oct 23 '17 at 20:32
@jarlh, I agree. Update made - I wasn't sure on best practice here but your solution looks cleaner to me. – kjmerf Oct 23 '17 at 20:39

Khandakar Rashed Hassan · Accepted Answer · 2018-04-02T16:28:02.153

0

You can use like function. The statement will look like this:

SELECT * FROM TABLE_NAME WHERE STATE_COLUMN LIKE 'STATE NAME' OR LIKE 'STATE ABBREVIATIONS'

Hopefully it will work.

edited Apr 02 '18 at 16:28

answered Oct 23 '17 at 20:15

Khandakar Rashed Hassan

140
2
8

Why `LIKE` without wildcards? What's wrong with `=`? – jarlh Oct 23 '17 at 20:22
1

String constants need to be enclosed in single quotes in SQL. Double quotes are for identifiers. – Oct 23 '17 at 20:23
This is syntactically incorrect and doesn't seem to do much that is useful. How could it be accepted? – Gordon Linoff Oct 23 '17 at 21:24

Location name inconsistency in database

2 Answers2