MySQL Query using substr() and instr() fails if needle is not found in haystack

Question

I need to generate a pseudo-key column in a MySQL table that's defined as the first part of the point_title field (up to a space, if any) concatenated with the record id.

Example data:

id      point_title       pseudo_key
_____________________________________
123     Scott R.          scott123
124     John R.           john124
125     Stacey J.         stacey125
126     Ted               ted126

To do this, I'm creating a trigger program that'll auto-fill this field upon update/insert:

    DROP TRIGGER IF EXISTS `point_name_update`//
    CREATE TRIGGER `point_name_update` 

    BEFORE UPDATE ON `map-points`
     FOR EACH ROW BEGIN
        SET NEW.point_name = concat(trim(lower(SUBSTRING(NEW.point_title,1,INSTR(NEW.point_title, ' ')))),NEW.id);
      END
    //

However, I can't figure out how to handle the case where Ted doesn't have a space in his point_title field. The pseudo_key value for that record is just '126'.

Does anyone have an idea for this?

Thanks, Scott

Answer 1

There are multiple ways to skin this cat and I found that I can use SUBSTRING_INDEX(), instead:

DROP TRIGGER IF EXISTS point_name_update// CREATE TRIGGER point_name_update

BEFORE UPDATE ON map-points FOR EACH ROW BEGIN SET NEW.point_name = concat(SUBSTRING_INDEX(LOWER(TRIM(NEW.point_title)), ' ', 1),NEW.id); END