Consider this stored procedure:
-- finds the first slash and exits
create procedure p_find_slash(p_text text)
declare v_index int default 1;
declare v_length int default character_length(p_text);
declare v_char char(1);
_main_loop: while v_index <= v_length do -- loop over all characters
set v_char := substring(p_text, v_index, 1); -- grab the current character
when v_char = '/' then -- found a slash!
select concat('A slash at ', v_index) message; -- report it
leave _main_loop; -- and then stop
select concat(v_char, ' is not a slash.') message; -- not a slash...
set v_index := v_index + 1; -- next character pls
Of course, it's a bogus stored procedure, but that's not the point right now. Most people can guess what the intention is of this procedure: it should examine each character in the argument text, check if it is a slash, and if so, report its position and then stop execution. If the character is not a slash, the procedure moves on to the next character.
So, what do you expect when we call it with only a literal slash as argument? Let's find out:
Well? It may come as a surprise to some, but this is the result:
| message |
| / is not a slash. |
So, can you explain this result? Can you fix it?
Just leave a comment to this post with your explanation and the solution. Results published later this week....and oh!! If you know how to fix this, maybe you're ready to move on to the next level and should attend the "Advanced Stored Procedures" tutorial by Mariella Di Giacomothe at the MySQL User's Conference. Like Giuseppe Maxia wrote earlier, you can earn a 20% discount code by asking a speaker!!
So that is the return: if you post your solution as a comment on this blog, I'll make sure you'll get that code for a 20% discount.