0

Essentially, I have a table called Table1 that has a column called emails that contains email addresses separated by semicolons.

For example:

Row1:

test1@gmail.com; test2@gmail.com

Row2:

test4@gmail.com; test5@gmail.com; test6@gmail.com

I would like to do a select query on the table to display each email on its own row each time the a semicolon is there to split. How can this be done?

John
  • 965
  • 8
  • 16
  • Don't put more that one value in a column for a single row. This isn't the way SQL can work easily. Use a separate table and a JOIN. So its currently not possible in its current form. – danblack Jun 20 '21 at 23:27
  • 1
    Normalize your schema in the first place. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes, it is.). – sticky bit Jun 20 '21 at 23:28
  • I understand, is there a way to use temp tables in the query to split up the data? – John Jun 20 '21 at 23:30
  • look for splitting string functions – nbk Jun 20 '21 at 23:33
  • You can find many posts on Stack Overflow with the search terms: "split string into rows" or similar. I voted to mark this question as a duplicate, after I found one such post that had a good variety of different solutions. None of them are very convenient or elegant. It's better to store your data using one row per value, as other people have commented. – Bill Karwin Jun 20 '21 at 23:42
  • with mysql 8+ or mariadb 10.2+: https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=4336e33289d44baf75333f6097554702 with earlier versions, you can use subqueries and joins but ideally need to know some maximum number of things that will need to be split – ysth Jun 21 '21 at 02:21

1 Answers1

-1

its better to make an array or array of object and save data like..

[test1@gmail.com, test2@gmail.com, ...]
[{email: test1@gmail.com}, {email: test2@gmail.com}]

in that way you can easily get data by using query or by any programming language you are working on.

Hasham Siddiqui
  • 44
  • 3