Is there any query to fetch the a single row data in chunks?

Question

The data stored in a row is just like "Phone Number:--- xxxxxxxxxxx Call Type:--- MISSED Call Date:--- 2013-06-13 Call duration in sec :--- 0 Phone Number:--- xxxxxxxxxxx Call Type:--- OUTGOING Call Date:--- 2013-06-14 Call duration in sec :--- 21 Phone Number:--- xxxxxxxxxxx Call Type:--- INCOMING Call Date:--- 2013-06-14 Call duration in sec :--- 79".

Is there any query that may help me to retrieve this row in parts or chunks.

Answer 1

I worked for a while on your request dude... your problem is that it's not really adapted for relational database (POOR PERFORMANCES), you will be obliged to use some scripting language, but there is some interesting results:

I've created a tale with one line:

CREATE TABLE mytable (mycolumn VARCHAR(1000));

Inserted your example in it:

INSERT INTO mytable VALUES ("Phone Number:--- xxxxxxxxxxx Call Type:--- MISSED Call Date:--- 2013-06-13 Call duration in sec :--- 0 Phone Number:--- xxxxxxxxxxx Call Type:--- OUTGOING Call Date:--- 2013-06-14 Call duration in sec :--- 21 Phone Number:--- xxxxxxxxxxx Call Type:--- INCOMING Call Date:--- 2013-06-14 Call duration in sec :--- 79");

Now with my query you can retrieve one line in your dataset. This query will split the result and return one line, you just have to modify the @i variable to choose your line (0 is the first line) :

SET @i = 0;
SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 3),' ', -1) AS "Phone Number",
SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 6),' ', -1) AS "Phone Type",
SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 9),' ', -1) AS "Call Date", 
SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 15),' ', -1) AS "Call Duration in sec"
FROM mytable;

Example with the third line:

[11:42:02] [DEV\MFO] mysql> SET @i=2;
Query OK, 0 rows affected (0.00 sec)

[11:42:08] [DEV\MFO] mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 3),' ', -1) AS "Phone Number", SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 6),' ', -1) AS "Phone Type", SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 9),' ', -1) AS "Call Date",  SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 15),' ', -1) AS "Call Duration in sec" FROM mytable;
+--------------+------------+------------+----------------------+
| Phone Number | Phone Type | Call Date  | Call Duration in sec |
+--------------+------------+------------+----------------------+
| xxxxxxxxxxx  | INCOMING   | 2013-06-14 | 79                   |
+--------------+------------+------------+----------------------+
1 row in set (0.00 sec)

Note that if you want to know how many lines you have in your dataset you can use:

SELECT (LENGTH(mycolumn) - LENGTH(REPLACE(mycolumn, ' ', '')) + 1) / 15 FROM mytable;

Let me know...

Max.