SQL Anywhere 10 should already have a mechanism built in for this purpose.

Have you looked at the sa_split_list procedure?

sa_split_list system procedure

With your two examples you can execute:

CALL sa_split_list('1,2,3');

or

CALL sa_split_list('A,B,C');

This procedure also allows for different delimiters, so you could split the list on semi-colons, spaces, etc.

Also, there are two columns presented by the stored procedure, but you could match your desired output by using a select list with an alias.

I.e.

SELECT row_value as testRecord FROM sa_split_list('A,B,C');

On a separate note, the sp_split_new_onet('A,B,C') is failing because it is looking for columns named A, B, and C; when executing sp_split_new_onet('1,2,3'), it succeeds because 1, 2, and 3 are being parsed as literals instead of column names.

Hope This Helps!

Since sa_split_list was introduced in version 10, you might need to resort to something else in earlier versions, something like the following may work (untested however):

CREATE PROCEDURE sp_split_list(str LONG VARCHAR, delim LONG VARCHAR)
RESULT(record LONG VARCHAR)
BEGIN
    DECLARE last_pos INTEGER;
    DECLARE next_pos INTEGER;

    IF (str = '') THEN
        SELECT '';
        RETURN;
    END IF;

    SET next_pos = LOCATE(str,delim);
    SET last_pos = 1;

    CREATE TABLE #temp (
        record LONG VARCHAR
    );

    WHILE (next_pos > 0) LOOP
        INSERT #temp VALUES(SUBSTR(str,last_pos,next_pos-last_pos));
        SET last_pos = next_pos+LENGTH(delim);
        SET next_pos = LOCATE(str,delim,last_pos);
    END LOOP;
    INSERT #temp VALUES(SUBSTR(str,last_pos));

    SELECT * FROM #temp;
END;

CALL sp_split_list('my,string,to,split',',');

This approach steers away from REPLACE and EXECUTE IMMEDIATE, helping to avoid SQL injection or other potential pitfalls.