Visit Our UNIX and Linux User Community


MySql split rows


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting MySql split rows
# 1  
Old 08-14-2013
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:
Code:
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:
Code:
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:
Code:
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:
Code:
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
# 2  
Old 08-15-2013
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:
Code:
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 -

Code:
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.

Code:
--
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:

Code:
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.)

Last edited by durden_tyler; 08-16-2013 at 09:08 AM..
These 2 Users Gave Thanks to durden_tyler For This Post:
# 3  
Old 08-17-2013
Awesome durden_tyler, it works perfect!
Many many thanks! SmilieSmilieSmilie

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:

Code:
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


Previous Thread | Next Thread
Test Your Knowledge in Computers #514
Difficulty: Easy
As a general rule, the more a variable is used, the longer the variable name should be.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to split one long column into multiple rows with 3 each ?

I have a large csv dataset like this : A value1 A value2 A value3 B value1 B value2 B value3 C value1 C value2 C value3 what I expected output is :A value1 value2 value3 B value1 value2 value3 C value1 value2 value3 I'm thinking of use like awk, columns , but haven't find a proper... (4 Replies)
Discussion started by: nengcheng
4 Replies

2. UNIX for Beginners Questions & Answers

Split column into rows

Hi, I have input dataset as below: Cl.jenn,1051,ABCD JEN.HEA,9740|1517|8119|2145,ZZZZ,REPEAT Rich.Sm, Ann.Car,3972|4051|1064|4323|4122|2394|2574|4507 Sta.for,7777,ABCD,UUUU Sm.Ric, Ch.LRD, Eh.ab, Gr.sh, Expected output: ------------------- Cl.jenn,1051,ABCD... (6 Replies)
Discussion started by: saravananravim
6 Replies

3. Shell Programming and Scripting

Split columns into rows

Any one can help me in converting columns into rows. example I have input file 10000| 10002| 10003| 10004| 10005| I want output in below format PARTY|PART_DT 10000|12080000000 10002|13075200000 10003|13939200000 10004|1347200000 10004|133600000 10004|1152000000 (13 Replies)
Discussion started by: syd
13 Replies

4. UNIX for Dummies Questions & Answers

Mysql: How to update value in 27000 rows?

Hello, some member created 27000 posts in wrong section (lol :D) so i need to edit all his entries to get new section ID. SELECT * FROM `phpbb_topics` WHERE `topic_first_poster_name` LIKE "%ozerway%"; this will select all his topics... the column with forum id is named "forum_id" and... (3 Replies)
Discussion started by: postcd
3 Replies

5. Shell Programming and Scripting

split paste them in rows

Hi, I have a file as ABC 123_456_789 234_678_901 XYZ 1100_1250_1580_1680 1175_1440_1620_1890 so on What I want my output file to look is "split by underscore and then place the contents in rows" output ABC 123 234 ABC 456 678 ABC 789 901 XYZ 1100 1175 XYZ 1250 1440... (3 Replies)
Discussion started by: Diya123
3 Replies

6. Programming

Getting Rows from a MySQL Table with max values?

I feel stupid for asking this because it seems that MYSQL code isn't working the way that I think it should work. Basically I wrote code like this: select * from `Test_DC_Trailer` HAVING max(DR_RefKey); Where the DR_RefKey is a unique numeric field that is auto iterated (like a primary key)... (7 Replies)
Discussion started by: Astrocloud
7 Replies

7. Shell Programming and Scripting

Split single rows to multiple rows ..

Hi pls help me out to short out this problem rm PAB113_011.out rm: PAB113_011.out: override protection 644 (yes/no)? n If i give y it remove the file. But i added the rm command as a part of ksh file and i tried to remove the file. Its not removing and the the file prompting as... (7 Replies)
Discussion started by: sri_aue
7 Replies

8. Web Development

[MYSQL] problem with spaces in rows

Hello. I'm not sure how I can get around this, or what I am doing wrong, but I need some help. :) I want to do an select query looking like this: SELECT venue, SUM( amount ) FROM IWD WHERE venue = 'Foxy Hollow' Unfortunately I need to have spaces in the names in these fields, is... (10 Replies)
Discussion started by: noratx
10 Replies

9. Shell Programming and Scripting

Split rows

Hi all, I need a simple bin/sh script FILE1: ab1 gegege swgdeyedg ac2 jxjjxjxjxxjxjx ad3 ae4 xjxjxj zhzhzh ahahs af5 sjsjsjs ssjsjsjsj sjsjsj ag6 shshshshs sjjssj shhshshs myScript.sh has to return: ROW ab1 ROW ac2 ROW ad3 ROW ae4 In other words: "ROW " + the first world... (3 Replies)
Discussion started by: ric79
3 Replies

10. Shell Programming and Scripting

split rows

Hi I wanted to split rows based on the number of 1's present in 21st field(21st field is 40 length field) so I wrote the below awk code. However, the tool that I am using to invoke the command is not recognising the command. So, could you please help me to translate this command to sed? awk... (5 Replies)
Discussion started by: ahmedwaseem2000
5 Replies

Featured Tech Videos