How can I separate a string into columns, if I have multiple rows and the strings are of different length in SQL?

Question

The question says:

Retrieve all unique countries of all publishers.

The column from where I'm supposed to retrieve the countries is the following:

Table: Publisher

Column: Address

Rows:

Hoboken, New Jersey, United States
London, United Kingdom
New York, New York, United States
New York, New York, United States
New York, New York, United States

Now, I understand that the answer is asking me is to display: Unites States, United Kingdom. However, the countries are in a string with city, state and country in the case of USA and city and country in the case of UK.

I don't know how to separate the column address into columns, so then I can retrieve the unique values for the countries.

I tried using SUBSTR() and SUBSTRING(), but I have to write the string inside, and I don't know how to write it all into one function.

You might want to advise the one who gave you the exercise to learn about normalization and don't teach their students such a horrible design. — sticky bit
– sticky bit, Commented Mar 5, 2020 at 14:22
If you assume the country is always last, it's everything after the last ', '. To find the last occurrence of something, reverse the string first and search that reversed string. If you have difficulties with some specific code, by all means ask that question, but for now I recommend reading tutorials or manuals on REVERSE, LOCATE, CHAR_LENGTH and SUBSTRING. Here's a starter: stackoverflow.com/questions/12775352/… — MatBailie
– MatBailie, Commented Mar 5, 2020 at 14:26
@NicoHaase Tried using a combiation of SUBSTR(), SUBTRING() AND LOCATE(), But I had to manually write each string and then I was able to just get either United Kingdom or United States, but that was manually done. I still don't know how to do it referencing the Address column to be bale to incorporate it in my final code to select Unique/Distinct values. — GabyB.
– GabyB., Commented Mar 5, 2020 at 15:36

sirine · Accepted Answer · 2020-03-05 14:48:10Z

1

You can use distinct substring_index.

select distinct substring_index(Address,',',-1) from publisher;

You can find more explanation here

answered Mar 5, 2020 at 14:48

sirine

262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I separate a string into columns, if I have multiple rows and the strings are of different length in SQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related