Boasting? I guess so… 8)
December 21, 2007 Posted by Emre S. Tasci
Suppose that you’ve collected some data from the output of a program. Let’s say that some part of this data consists of Author names something similar to:
You want to split the initials from the surnames. This is piece of cake with PHP but I don’t want to go parsing each row of which there are many… So, take a look at this ugly beauty:
aaaaand here is what you get:
if you are thinking something similar to
UPDATE dbl004 SET val1 = LEFT(val,LOCATE(" ",val)-1), val2 = RIGHT(val,LENGTH(val)-LOCATE(" ",val));
or
UPDATE dbl004 set val1 = TRIM(SUBSTRING(SUBSTRING_INDEX(val,".",1),1,LENGTH(SUBSTRING_INDEX(val,".",1)) – LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(val,".",1)," ",-1)))), val2 = TRIM(SUBSTRING(val, LENGTH(SUBSTRING_INDEX(val,".",1)) – LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(val,".",1)," ",-1))));
Try to process these 3 values: "van der Graaf K.L. Jr.", "Not Available" and "Editor".
About this entry: I couldn’t refrain myself from boasting after I managed to come up with that beautiful MySQL query… sorry for that. (Yes, I know, superbia, the 7th and the most deadly…) So let me try to balance this arrogant entry of mine:
With my best regards,
Your humble blogger…
December 26, 2007 at 4:40 pm
#1-3
UPDATE dbl032 SET val1 = IF(LOCATE(“,”,val),TRIM(LEFT(val, LOCATE(“,”,val)-1)),val), val3 = TRIM(RIGHT(val,LENGTH(val)-LOCATE(“,”,val)));
#2-3
#UPDATE dbl032 SET val2 = IF(LOCATE(“,”,val3), TRIM(LEFT(val3,LOCATE(“,”,val3)-1)),val3), val3 = TRIM(RIGHT(val3,LENGTH(val3)-LOCATE(“,”,val3)));
#Clear All
#UPDATE dbl032 SET val1 = NULL, val3=NULL, val2=NULL
January 9, 2008 at 10:26 am
[…] can refer to my previous entry for slicing up the "val" column. I had already done this while constructing the […]
March 6, 2008 at 10:07 am
Looks like fun!
March 6, 2008 at 10:14 am
even more fun than it looks! 8)