MySql split rows

Dear community,
I have to split string in table and list all values. I'll skip the code and jump directly to mysql query.

This is the table:

category     title
=======     =======
7,3         title 1
1,3         title 2
1,2,3       title 3

Now, what I need is split category into single value like:

category     title
=======     =======
7           title 1
3           title 1
1           title 2
3           title 2
1           title 3
2           title 3
3           title 3

I tried with the "classic" function SPLIT_STR:

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

And then the query:

SELECT SPLIT_STR(category, ',', 1), title
FROM `post`

But in this case I got only the first value of each row.

Could someone advise?

Thanks
Lucas

Oracle and PostgreSQL have nice techniques to generate a series of unique numbers, one per line.
Unfortunately MySQL doesn't, so create a view with a pre-defined number of rows and use it to join with the "post" table. In order to determine the number of rows in this view, which I will call "iterator", find out the maximum number of tokens in your "category" column.

As an example, if the "category" column has the value:

1,2,3,4,5,6,7

then it has 7 tokens i.e. 7 numbers that are separated by commas. Similarly, find out the maximum number of tokens you require.

Let's assume the maximum number of tokens is 5. Then the view definition looks like this -

create view iterator (x)
as
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5
;

Thereafter, your query should be straightforward.

--
select substring_index(substring_index(p.category,',',iter.x), ',', -1) as category,
       p.title
  from post p,
       iterator iter
 where iter.x <= length(p.category)-length(replace(p.category,',',''))+1
 order by p.title, iter.x
;

A test run follows:

MariaDB [test]> 
MariaDB [test]> 
MariaDB [test]> -- Show the data in the "post" table
MariaDB [test]> select * from post;
+----------+---------+
| category | title   |
+----------+---------+
| 7,3      | title 1 |
| 1,3      | title 2 |
| 1,2,3    | title 3 |
+----------+---------+
3 rows in set (0.00 sec)

MariaDB [test]> 
MariaDB [test]> -- Show the contents of my script "test_qry.sql"
MariaDB [test]> \! cat -n test_qry.sql
     1    --
     2    select substring_index(substring_index(p.category,',',iter.x), ',', -1) as category,
     3           p.title
     4      from post p,
     5           iterator iter
     6     where iter.x <= length(p.category)-length(replace(p.category,',',''))+1
     7     order by p.title, iter.x
     8    ;
     9    
MariaDB [test]> 
MariaDB [test]> -- Run the script "test_qry.sql"
MariaDB [test]> source test_qry.sql
+----------+---------+
| category | title   |
+----------+---------+
| 7        | title 1 |
| 3        | title 1 |
| 1        | title 2 |
| 3        | title 2 |
| 1        | title 3 |
| 2        | title 3 |
| 3        | title 3 |
+----------+---------+
7 rows in set (0.00 sec)

MariaDB [test]> 
MariaDB [test]> 
MariaDB [test]> 

Hope that helps.

(Please disregard the "MariaDB" prompt. It's just a fork of MySQL provided by Fedora 19. MySQL's acquisition by Oracle has raised concerns that it might become closed-source.)

Awesome durden_tyler, it works perfect!
Many many thanks!

Now I have only to adapt my final query, because I have to take data from 3 different tables. Hope your solution can be applied to the following query:

SELECT`post`.id,`post`.title,`post`.alt_name post_alt_name,`post`.date,`post`.category,`post`.comm_num,`post`.autor,`category`.name,
`category`.alt_name cat_alt_name,`post_extras`.news_read
FROM`post`AS`post` ,`category`AS`category` ,`post_extras`AS`post_extras` 
WHERE`post`.category !=69
AND`post`.category =`category`.id
AND`post`.id =`post_extras`.news_id
ORDERBY`category`.id,`post`.title