Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Your example shows three "sets" of data in the one row; will there always be three? Can you change the formatting at all (to put a delimiter between the "sets", or between the fields? –  Alain Collins Jun 16 '13 at 5:04
    
Yes, i have done almost the same thing that you've said. Before the Phone Number, i have put # symbol so that i can differentiate between the records. –  Ali Ansari Jun 16 '13 at 19:10
    
And thnx for the reply, i followed this...and it works fine –  Ali Ansari Jun 16 '13 at 19:11
add comment

1 Answer

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.

share|improve this answer
    
Thank u So Much for the help,,,it works... –  Ali Ansari Jun 16 '13 at 19:07
    
You're welcome. –  mfouilleul Jun 17 '13 at 6:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.