0

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.

GabyB.
  • 3
  • 1
  • 5
    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 Mar 05 '20 at 14:22
  • 1
    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: https://stackoverflow.com/questions/12775352/last-index-of-a-given-substring-in-mysql – MatBailie Mar 05 '20 at 14:26
  • What have you tried so far? Where are you stuck? – Nico Haase Mar 05 '20 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. Mar 05 '20 at 15:36

1 Answers1

1

You can use distinct substring_index.

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

You can find more explanation here

sirine
  • 26
  • 2