Deal with semicolon separated data in MySQL table

Question

I've got MySQL DB with multiple data in one column separated by semicolon. I need to use the first of them. What is the best recommended way how to deal with this kind of stored data? (for this specific problem and also generally how to use semicolon separated data).

Normalise your data, do not store delimited data in single field. See http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad for detailed reasons why — Shadow, Mar 03 '17 at 15:00

score 0 · Answer 1 · answered Mar 03 '17 at 15:00

0

Use

SELECT SUBSTRING_INDEX(column_name, ';', 1) from your_table

answered Mar 03 '17 at 15:00

Utsav

7,914
2
17
38

1

You have been around long enough to know that you should explain your answer codes. – Blackbam Mar 03 '17 at 15:40
I would have if it was complex – Utsav Mar 03 '17 at 15:44

spencer7593 · Accepted Answer · 2017-03-03T15:36:36.100

Q: "What is the best recommended way how to deal with this kind of stored data?"

A: The best recommendation is to avoid storing data as comma separated lists. (And no, this does not mean we should use semicolons in place of commas as delimiters in the list.)

For an introductory discussion of this topic, I recommend a review of Chapter 2 in Bill Karwin's book: "SQL AntiPatterns: Avoiding the Pitfalls of Database Programming"

Which is conveniently available here

https://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557

and from other fine booksellers. Setting that recommendation aside for a moment...

To retrieve the first element from a semicolon delimited list, we can use the SUBSTRING_INDEX function.

As a demonstration:

SELECT SUBSTRING_INDEX('abc;def;ghi',';',1)

returns

'abc'

The MySQL SUBSTRING_INDEX function is documented here: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index

I recognize that this might be considered a "link only answer". A good answer to this question is going to be much longer, giving examples to demonstrate the pitfall of storing comma separated lists.

If the database will only ever view the comma separated list as a blob of data, in its entirety, without a need to examine the contents of the list, then I would consider storing it, similar to the way we would store a .jpg image in the database.

I would store and retrieve a .jpg image as a BLOB, just a block of bytes, in its entirety. Save the whole thing, and retrieve the whole thing. I'm not ever going to have the database manipulate the contents of the image. I'm not going to ever ask the database to examine the image to discern information about what is "in" the jpg image. I'm not going to ask the database to derive any meaningful information out of it... How many people are in a photo, what are the names of people in a photo, add a person to the photo, and so on.

I will only condone storing a comma separated (or semicolon separated) separated list if we are intending it to be an object, an opaque block of bytes, like we handle a jpg image.

Deal with semicolon separated data in MySQL table

2 Answers2