Using LOCATE string function in stored procedure

Question

Is anyone familiar with using the LOCATE with a passed in string varialbe? I am trying to use it to determine if there is a comma in a string that was set in the stored procedure but have not been able to get it to work properly. My code looks something like this

    DECLARE string VARCHAR(10);
    DECLARE comma_found INT;

    SET string = 'hello, world';

    SET comma_found = SELECT LOCATE(',',string);

    IF( comma_found <> 0 ) THEN

         ...execute code....

    END IF;

This code will not complie because of the SELECT LOCATE and I can not figure out what is wrong. Is it my syntax? Usage? Is there any other string maniuplation function I can use to accomplish this? I am doing this within a stored procedure in Mysql.

Answer 1

Well the syntax for LOCATE is LOCATE(substring, string). Your query literally says LOCATE(',', string) where string is a varchar(10). Your string is actually more than 10 characters long though =P

Answer 2

I haven't used MySQL in a long time so I'm not completely familiar with the newer syntax but from some googling, I believe

SET comma_found = SELECT LOCATE(',',string);

should be

SELECT @comma_found := LOCATE(',',string);

then you can use @comma_found eg:

SELECT @comma_found

It seems that you're trying to assign a data set to the variable rather than the result of the LOCATE

See here: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html