Parsing a value separated by a dash in an SQL table and doing a search

Question

How to syntactically differentiate values ​​in a spreadsheet in an SQL table and use different parsing values ​​to search in other tables?

Let's say I have user.nationality, which is varchar as "41-33-11". I have tables of countries, cities, and states associated with these identifiers, 41 (Buffalo), 33 (New York), and 11 (USA).

SELECT users.name, users.email, users.nationality FROM users

nationality = "41-33-11"

How can I create a SELECT statement to get "Buffalo, NY, USA"?

Answer 1

Use the inline view to pull the values ​​so you can then join them:

JOIN (SELECT SUBSTR(t.nationality, 0, 2) AS city_id, SUBSTR(t.nationality, INSTR(t.nationality, '-')+1, 2) AS state_id, RIGHT(t.nationality, 2) AS country_id FROM USERS t) u JOIN COUNTRY cntry ON cntry.country_id = u.country_id JOIN CITY cty ON cty.city_id = u.cty_id JOIN STATE st ON st.state_id = u.state_id 

Answer 2

Look at this answer , especially look at SUBSTRING_INDEX()

Answer 3

There are a bunch of string split solutions in mysql dev comments , with which you can use the split line in the WHERE IN statement.

Answer 4

depends on the SQL you are using. I would say that this was easier to do in the language you are querying for, for example using PHP split (). If it's MySQL, then this article should help: http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

MS SQL has built-in partition functions, I think.