Inserting a delimiter

Question

MySql has a function CONCAT_WS that I use to export multiple fields with a delimiter into a single field. Works great!

There are multiple fields being stored in a database I query off of that has data that I need to extract each field individually but within each field the data need to include a delimiter. I can most certainly do a concatenate but that does take awhile to set-up if my data requires up to 100 unique values. Below is an example of what I am talking about

Stored Data  01020304050607                    
End Result   01,02,03,04,05,06,07

Stored Data  01101213
End Result   01,10,12,13

Is there a function in MySQL that does the above?

Then the question is: why do you store the data this way, if you need to retrieve it in a very different format? — Jocelyn, Jul 15 '16 at 15:38
better yet, while you are splitting it, don't save your data with CSV. Create new rows. If you find yourself using find_in_set and group_concat on a daily basis to survive your data concoctions, you are probably doing something wrong. See [Junction Tables](http://stackoverflow.com/a/32620163) if so. For 1 to many, versus many-to-many, same concept. Don't save CSV data. — Drew, Jul 15 '16 at 16:51
Sorry, but I was not around when the data schema was created. Also, I am not on the tech side. Just a user of the database. I figured out an alternative to this without having to create a procedure/function. I am using Excel to create my query based on extracting the field name and response values from the data dictionary. — John K, Jul 15 '16 at 17:09

score 0 · Answer 1 · answered Jul 15 '16 at 17:19

I am not that familiar with mysql but I have seen questions like this come up before where a regular expression function would be useful. There are user-defined functions available that allow Oracle-like regular expression functions to be used as their support is weak in mysql. See here: https://github.com/hholzgra/mysql-udf-regexp

So you could do something like this:

select trim(TRAILING ',' FROM regexp_replace(your_column, '(.{2})', '\1,') )
from your_table;

This adds a comma every 2 character then chops off the last one. Maybe this will give you some ideas.

Inserting a delimiter

1 Answers1