tips and tricks

When you compile any procedure with '\' in Sybase IQ, your code will be replaced with '\\' to avoid whis
use CHAR(92) instead.

CREATE PROCEDURE MY_TEST
BEGIN
        SELECT LIST(TABLE_NAME, CHAR(92)) FROM SYSTABLES;
END;
/

Oracle's LTRIM/RTRIM supports second parameter to remove other characters than blank.
For example
select LTRIM('My Textaaaa', 'a') from dual; will return 'My Text'.
Same behavior can be simulated with Sybase IQ as well

LTRIM:
substr(<STRING>, length(<STRING>)-length(ltrim(replace( <STRING> ,<CHARACTERS TO REMOVE>,' ')))+1)

RTRIM:
substr( <STRING> ,1,length(rtrim(replace( <STRING> , <CHARACTERS TO REMOVE> ,' '))))

Sybase IQ equavalent for Oracle wm_concat and SQL group_concat is LIST() function.

This function takes 2 parameters column_name (mandotory) and separator (optional default ",").

Sample query:
select list(table_name) from systable

Click for related Sybase Central entry

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