Oracle - Builtin Functions

oracle - COALESCE - return the first non-null

Example of using substr:

SUBSTR( string, start_position [, length ] );
SELECT TO_NUMBER(SUBSTR('20103',0,4)) FROM DUAL; // This will return 2010

Example of replacing control characters using :

UPDATE report.qreport_params SET DEFAULT_VALUE=regexp_replace(DEFAULT_VALUE,'[[:cntrl:]]','') WHERE qreports_id = 30784 AND qparams_id=49;

What is the equivalent of MySQL group_concat?


SELECT WM_CONCAT(OT_ID) FROM schemaName.tableName WHERE columnName=555001621;
SELECT question_id,
       LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)

How can we use the TRIM function?

SELECT TRIM(BOTH ' ' FROM columnName) TRIMMED_LOGINID FROM schemaName.tableName;

How to use the NVL function?


NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1. The arguments expr1 and expr2 can have any datatype. If their datatypes are different, then Oracle Database implicitly converts one to the other. If they are cannot be converted implicitly, the database returns an error. See

How to use the NVL2 function?

NVL2 (expr1 , expr2 , expr3)

The NVL2 function returns the value of expr2 when the value of expr1 is not NA or an empty string, or returns the value of expr3 if the value of expr1 is an empty string or NA. In other words, if expr1 is true, nvl2 returns expr2, and if expr1 is false, nvl2 returns expr3. When the data types of expr2 and expr3 are different, then the function converts expr3 to the data type of expr2 before comparing them.

How to use the decode function?

The decode function allow us to translate the values in the database column to another values.

SELECT decode(columnName, 'v1', 'translatedTo1', 'v2', 'translatedTo2', 'Unknown')
SELECT decode(emp_sex, 'M', 'Male',
                  'F', 'Female', 'Unknown')
FROM   employees;

SELECT DECODE (SIGN (Invoice_Date - Order_Date), 1, Order_Date, Invoice_Date)

See decode

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License