Hex, Bugs and More Physics | Emre S. Tasci

a blog about physics, computation, computational physics and materials…

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:


UPDATE dbl004 set val1 = IF(LOCATE(".",val),TRIM(SUBSTRING(SUBSTRING_INDEX(val,".",1),1, LENGTH(SUBSTRING_INDEX(val,".",1)) – LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(val,".",1)," ",-1)))),val), val2 = IF(LOCATE(".",val), TRIM(SUBSTRING(val,LENGTH(SUBSTRING_INDEX(val,".",1)) – LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(val,".",1)," ",-1)))),"");

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));



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…

4 Responses to “Boasting? I guess so… 8)”

  1. admin Says:

    UPDATE dbl032 SET val1 = IF(LOCATE(“,”,val),TRIM(LEFT(val, LOCATE(“,”,val)-1)),val), val3 = TRIM(RIGHT(val,LENGTH(val)-LOCATE(“,”,val)));
    #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

  2. Hex, Bugs and More Physics | Emre S. Tasci » Blog Archive » Less is More MySQL Says:

    […] can refer to my previous entry for slicing up the "val" column. I had already done this while constructing the […]

  3. Marcel Says:

    Looks like fun!

  4. admin Says:

    even more fun than it looks! 8)

Leave a Reply