MySQL stored procedure behaving unpredictably -

writing stored procedure parse names in format 'last first mid' , 'last first mid suffix' table.

---

create procedure parse_full_name(full_name text)   begin     set @last_name = substring_index(full_name, ' ', 1);     set @middle_name = substring_index(full_name, ' ', -1);     set @first_name = substr(full_name, locate(' ', full_name), length(full_name) - length(@middle_name) - length(@last_name));      set @query = concat('select ''', @last_name,''' last_name, ''', @first_name,''' first_name, ''', @middle_name,''' middle_name;');     prepare stmt @query;     execute stmt;     deallocate prepare stmt;   end; 

---

call parse_full_name('last first middle');  >>>  last_name | first_name | middle_name >>> -----------|------------|------------- >>>  last      | first      | middle  call parse_full_name('last first middle suffix');  >>>  last_name | first_name   | middle_name >>> -----------|--------------|------------- >>>  last      | first middle | suffix 

---

does what's expected. let's handle suffix case:

create procedure parse_full_name(full_name text)   begin # assume full_name 'last first middle' until proven not     set @last_name = substring_index(full_name, ' ', 1);     set @middle_name = substring_index(full_name, ' ', -1);     set @first_name = substr(full_name, locate(' ', full_name), length(full_name) - length(@middle_name) - length(@last_name));      # check if full_name 'last first mid suffix'     # if first_name contain 'first middle'     if locate(' ', @first_name) # if statement evaluates true       set @last_name = concat(@last_name, ' ', @middle_name);       set @middle_name = substring_index(@first_name, ' ', -1); # mysql seems skip line of code       set @first_name = substring_index(@first_name, ' ', 1);   # line     end if;      set @query = concat('select ''', @last_name,''' last_name, ''', @first_name,''' first_name, ''', @middle_name,''' middle_name;');     prepare stmt @query;     execute stmt;     deallocate prepare stmt;   end; 

---

results:

call parse_full_name('last first middle jr'); 

expecting:

>>>  last_name | first_name | middle_name >>> -----------|------------|------------- >>>  last jr   | first      | middle 

but get:

>>>  last_name | first_name | middle_name >>> -----------|------------|------------- >>>  last jr   |            | 

and better:

call parse_full_name('last first middle');  >>>  last_name   | first_name | middle_name >>> -------------|------------|------------- >>>  last middle |            | 

---

so, where's problem? i'm not mysql pro, looks logically , intuitively correct.

rookie mistake. had trim strings.

set @len_first_name = length(full_name) - length(@middle_name) - length(@last_name)); set @first_name = substr(full_name, locate(' ', full_name), @len_first_name); 

@len_first_name grabs space between first , middle, @first_name ends returning ' first middle', explain why locate(' ', @first_name) returning true.

---

set @last_name = trim(substring_index(full_name, ' ', 1)); set @middle_name = trim(substring_index(full_name, ' ', -1)); set @len_first_name = length(full_name) - length(@middle_name) - length(@last_name); set @first_name = trim(substr(full_name, locate(' ', full_name), @len_first_name)); 

and it's working.