SQL Splitting in MySQL without own functions

Question

I have a little problem with some strange strings in one's database table. I need to split such examples of strings to arrays of INT or separetely to INT looping or sth.

This must be done in 'usual' MySQL (no functions, declares etc - only SELECT statements + built-in-functions like REPLACE(), SUBSTRING())

524;779; 559;; ;559; 411;760;; + others;

Is such intention possible to perform?

what is your tables structure is? and what is your expected result?\ — Alex, Apr 27 '15 at 13:31
I need to retrieve each INT by sptlitting from field 'some_field' VARCHAR(50) which contains these examples - concated INTS with `;` after each — Rafcik, Apr 27 '15 at 13:34
This is the result of a bad database design. If you need to store a list of numbers associated to a particular row, then put them in a table with a reference (FK) to the row and the number. If you have 5 numbers, you will have 5 rows. I don't think that you can do anything hare - you're stuffed. If you can retrieve the data into a web page using whatever language you use (php, C#, etc) then you can do the conversion in the code and display however you want. But with SQL alone - nope. Change the database design to something proper. — simon at rcl, Apr 27 '15 at 16:39
Database was created in wrong way by another programmer, now my order is to recreate database in better way using relations and ids, not strange concats of strings.. I need int results from this joined strings. Thanks in advance :) — Rafcik, Apr 27 '15 at 19:18

score 0 · Answer 1 · edited May 23 '17 at 12:14

If this is a run-once operation to convert your data into a sane format, you could take the idea from this answer and extend it to the maximum number of ids in one string.

Example on SQL Fiddle.

SELECT DISTINCT id, val
FROM (
  SELECT id, substring_index(substring_index(`val`,';',-1),';',1) AS val FROM tab
  UNION ALL
  SELECT id, substring_index(substring_index(`val`,';',-2),';',1) FROM tab
  UNION ALL
  SELECT id, substring_index(substring_index(`val`,';',-3),';',1) FROM tab
  UNION ALL
  SELECT id, substring_index(substring_index(`val`,';',-4),';',1) FROM tab
  UNION ALL
  SELECT id, substring_index(substring_index(`val`,';',-5),';',1) FROM tab
) x
WHERE val <> ''
ORDER BY id

SQL Splitting in MySQL without own functions

1 Answers1