?
Solved

mySQL - help parsing column data

Posted on 2014-07-10
11
Medium Priority
?
250 Views
Last Modified: 2014-07-14
Hi..
I have a comments field with Error codes and messages. The data looks like this.
"Error 1.0  Big Error   Error 2.0  Disk Error   Error 3.0 I/O  Error"
I need to parse the 3 error codes out (Some may have 2 or more)
I need to use Error X.X as the 'delimiter' So I get 3 separate records

Error 1.0  Big Error  
Error 2.0  Disk Error
Error 3.0 I/O  Error

Any ideas?
thx
0
Comment
Question by:JElster
  • 6
  • 4
11 Comments
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 40188969
Are all the errors exactly   "Error n.0" where n is a single digit?
Are the n numbers always in order?

SInce you cannot cue on the word "Error", can you reliably cue on the dot to identify the separate fields?  How about THREE spaces that seem to separate the sections, can you reliably cue on that?
0
 
LVL 1

Author Comment

by:JElster
ID: 40188990
No.. I could be any number after the word 'Error'
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 40189036
Will it always be a number?   Will there be any other numbers present?

How about the three spaces?

Will there be any other dots in the entire field besides the ones in the numbers?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:JElster
ID: 40189318
Yes, no just single number like   1.0  99        123
at least 1 space after Error
Could be dots or periods in sentences.
thx
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 40189535
Will ever segment look like this?

"Error nnnn  Something Error"


Are there always THREE spaces between segments?

Do you have any control over how this string is originally formatted?

I'm trying to find a way to distinguish the "Error nnn" part from the "Something Error" part.
0
 
LVL 1

Author Comment

by:JElster
ID: 40189539
Yes..  It would look like that

Error 1  Something Error    Error 2   Something Error       Error 25.6  Something Error

The 'delimiter' will be  'Error N'

thx
0
 
LVL 27

Accepted Solution

by:
Cornelia Yoder earned 1980 total points
ID: 40189606
This should work for you:

<?php

$input = "  Error 1  Something1    Error  Error 2  Something2  Error   Error 25.6    Something3   Error  ";

//remove leading and trailing spaces and collapse all multiple spaces
trim($input);
str_replace("  "," ", $input);
str_replace("  "," ", $input);
str_replace("  "," ", $input);
str_replace("  "," ", $input);

//divide the string at the word "Error"
$array = explode("Error",$input);
$number = count($array);

//array[1] is the error number, $array[2] is the error name (without the word "Error", and so on in pairs.
//the echo statement replaces the word "Error" where it was removed by explode().
for ($i=1;$i<$number;$i=$i+2)
{
echo "Error " . $array[$i] . $array[$i+1] . "Error<br><br>";
}

//You can replace the echo statement with wherever you want the parsed lines to end up.
exit();
?>

Open in new window

0
 
LVL 1

Author Comment

by:JElster
ID: 40190570
Thx.. but I don't know or have PHP.
Can it be done in SQL?
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 40190818
yikes, I doubt it, there are too many insecure formatting problems, such as variable number of spaces, double use of the word "Error", etc.  Using explode() was the only way I could think of to do it, and MySQL doesn't have an equivalent function.

Is this format something you have control over?   Wherever this comments field is created, do you have the ability to change the format/contents?
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 40190836
I'm sorry but I will have to be gone most of the day today.   I'll check back tonight when I get home.  

In the meantime, see if you can follow what I did in php:
  1.  Strip out all extra spaces
  2.  Divide the string up based on the word "Error"
  3.  Take pairs of the remaining as Number/Description pairs
  4.  Reassemble as needed
and see if you can find some MySQL functions that will do something similar.


If you have control over the format, perhaps we can simplify the entire problem by changing the formatting.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40192872
We are looking at one row with three different data elements in the same column.  This is a mistaken design.  The correct way to organize this data is to have three rows, each with one of the data elements.  If you find that you have to "parse" any column of a data base, it's a definite code smell that suggests a need to reorganize the data.  I think the best way forward is to reorganize now, before it becomes more difficult and expensive.  Instead of inserting multiple data elements into the same column, make two separate INSERT statements to put the data elements into separate rows.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month16 days, 14 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question