tips and tricks

"Obsessively specialize. No niche is too small if it's yours."


For sure this is one of the best tips for successful career.
Knowing something about everything can be good in daily life but to climb steps in your career, know everything about something. It doesn't matter if it is small or big topic. nBe expert, get as many certificates as possible (if they exist), share your expertise online and build reputation around it.

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