MySQL Query using substr() and instr() fails if needle is not found in haystack
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
MySQL Query using substr() and instr() fails if needle is not found in haystack
===

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