Search code examples
mysqlsqlstringuniqueworkbench

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

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.

Solution

  • You can use distinct substring_index.

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

    You can find more explanation here