tips and tricks

Oracle Translate function equivalent for Sybase IQ

Oracle translate function has many great usages like Eliminating Double Quotes, Encryption/Decryption etc.

I just wrote SybaseIQ version of it. Code can easly be improved but version 1.0 is not a bad place to start.
Source code is below please feel free to use and comment.


CREATE FUNCTION TRANSLATE (
-- -------------------------------------------------------------------------------------
-- Designer    : Ongun Demirler
-- Author    : Ongun Demirler  
-- Description  :    Sybase IQ replacement for Oracle Translate function
--                    Overrides
--
-- Ammedments   :
--    When        Who                    What
--    ===========    ========            =========================================
--    03.04.2012    Ongun DEMIRLER        Initial design and implementation
--
--    Parameters:
--                P_STR: original string
--                P_SRC: source characters
--                P_DEST: destination characters
--  
-- -------------------------------------------------------------------------------------
    P_STR VARCHAR(4000),
    P_SRC VARCHAR(4000),
    P_DEST VARCHAR(4000) )
RETURNS VARCHAR(4000)
BEGIN
 
    DECLARE a_ret_str varchar(4000); --Return string
    DECLARE a_pointer integer; --Character pointer
    DECLARE a_src_length integer; --Length of source string
 
    set a_src_length = LENGTH(P_SRC);
    --No characters to translate from, source or to
    IF LENGTH(P_STR) = 0 OR a_src_length = 0 OR LENGTH(P_DEST) = 0 THEN RETURN ('') END IF;
 
    --Create replace statement for each character in p_src
    SET a_pointer = 1;
    SET a_ret_str = P_STR;
 
    --Replace each character from src with corresponding character from dest
    WHILE (a_pointer <= a_src_length) LOOP
        SET a_ret_str = replace(a_ret_str, SUBSTR(P_SRC, a_pointer, 1) , SUBSTR(P_DEST, a_pointer, 1));
        SET a_pointer = a_pointer + 1;
    END LOOP;
 
    RETURN (a_ret_str);
END