String manipulation and RIGHT() Problem

[Barak Mery]

Hi ,

I have a sp that manipulate strings.
The sp Get some data in theformat of Rows and Fields.
For exsample :
'111|222|333|444@555|666|777|888'
The @ seperates Rows.
The | seperates Fields.

Each row always contain 4 fields.
The number of row is not known.


The sp Manipulate the string and rutrn each "field" Seperatly.

When I call string_manipulation('111|222|333|444')
Everythings work fine and the result is :

111
222
333
444

The strange thing is that when i call string_manipulation('1|222|333|444')
The result is :
1
o2 <--- Here is the problem.
333
444

Instead of getting '222' i get some strange string in an un recognized charechter.

I'm using mysql 5.0.13, innodb, windows xp, the default charechter set for the db is hebrew.
Here is the SP.

DELIMITER $$

DROP PROCEDURE IF EXISTS `bcm`.`string_manipulation` $$
CREATE PROCEDURE `string_manipulation`(AllData VARCHAR(100))
BEGIN



#CALL sp_Export_list('1111|222||444')



DECLARE RowPos INTEGER;
DECLARE RowStr VARCHAR(100) DEFAULT '';
DECLARE FldPos INTEGER;
DECLARE FldStr VARCHAR(100) DEFAULT '';
DECLARE ChckRowState INTEGER DEFAULT 1;
DECLARE FldCount INTEGER;

SET AllData = CONCAT(AllData,'@') ;

REPEAT #rows Loop

SET RowPos = INSTR(AllData,'@'); #finds the First Row End Position

SET RowStr = LEFT(AllData,RowPos-1); #Put the first Row String into variable

SET FldCount = 1; #Initialize the field counter . the loop will run 4 times
#Ther are onlyqalways 4 fields

SET RowStr = CONCAT(RowStr,'|');

#SELECT RowStr;

REPEAT #fields Loop

SET FldPos = INSTR(RowStr,'|'); #finds the First Field End Position

SET FldStr = LEFT(RowStr,FldPos-1); #Put the first field String into variable

SELECT FldStr;

SET RowStr = RIGHT(RowStr,LENGTH(RowStr) - FldPos); # row string without the field already handled

SET FldCount = FldCount + 1;

UNTIL FldCount=5 END REPEAT;


SET AllData = RIGHT(AllData,LENGTH(AllData)-RowPos); # Alldata string without the Row already handled

IF INSTR(AllData,'@') > 0 THEN

SET ChckRowState=1;

ELSE

SET ChckRowState=0;

END IF;

UNTIL ChckRowState=0 END REPEAT;


END $$

DELIMITER ;

Thanks In Advance
Barak