Parsing a dash-delimited value in a SQL table and doing lookups.

Question

How do I parse a dash-delimited value in a SQL table and use the different parsed-out values to do lookups in other tables?

Say I have users.nationality, which is a varchar like "41-33-11". I have the country, city and state tables keyed to those ids, 41 ("Buffalo"), 33 ("NY") and 11 ("USA").

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

nationality = "41-33-11"

How do I craft a SELECT statement so to get "Buffalo, NY, USA"?

Answer 1

Have a look at this answer, particularly look at using SUBSTRING_INDEX()

Answer 2

Use an inline view to pull out the values so you can then join onto 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 3

There's a bunch of string splitting solutions among the mysql dev comments by which you can use the split string in a WHERE IN statement.

Answer 4

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

MS SQL has builting split functions I think.