CHARTOROWID converts a string representation of a ROWID to a ROWID value. The string argument can be a CHAR,VARCHAR2,NCHAR, or NVARCHAR2 data type to ROWID data type. Example: SELECT ID from some_table WHERE ROWID = CHARTOROWID(‘AAAAtmAAEAAAAFmAAA’); ID —— 22
Compose – Oracle SQL Function
COMPOSE allows you to compose a UNICODE string by passing in one or more code points. COMPOSE will add them all together and return the result. Example: SELECT COMPOSE(‘Let”s go to the cafe301 to get some tea.’)) as RESULT from
Convert – Oracle SQL Function
CONVERT converts the string argument from one character set to another one. Syntax: CONVERT(string,destination_set, source_set) The source_set argument specifies the character set of the stored data in the database if ‘string’ is a column name, or the character set of
To_clob – Oracle SQL Function
TO_CLOB converts character data to a CLOB. The string argument can be a CHAR, VARCHAR2, NCHAR NVARCHAR2, CLOB, or NCLOB data type. Example: SELECT TO_CLOB(‘SOME DATA’) as RESULT from DUAL; RESULT ———— SOME DATA
To_date – Oracle SQL Function
TO_DATE converts a string to a DATE. The string argument can be a CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. The format argument can be any valid date format. You can also specify the NLS_DATE_LANGUAGE parameter to change the language
To_number – Oracle SQL Function
TO_NUMBER converts argument 1 toa NUMBER value. Syntax: TO_NUMBER(arg1,format,params) The arg1 argument can be a BINARY_FLOAT, BINARY_DOUBLE,CHAR,VARCHAR2,NCHAR, or NVARCHAR2 data type. The format parameter can be any valid date format. Valid options for the ‘params’ argument: NLS_NUMERIC_CHARACTERS = To specify
Cardinality – Oracle SQL Function
CARDINALITY returns the number of records in a nested table. The function returns a NUMBER result. Example: SELECT ID,NAME,CARDINALITY(some_nested_table) FROM TABLE_NAME;
Collect – Oracle SQL Function
COLLECT is used with the CAST function to create a nested table of the specified column name and cast it to the type specified in the CAST function. Example: SELECT CAST(COLLECT(some_column) as some_type) FROM table_name;
Set – Oracle SQL Function
SET converts the nested table passed as an argument toa SET by removing any duplicate records from the table. Example: SELECT ID,NAME,SET(some_nested_table) FROM TABLE_NAME;
Ltrim – Oracle SQL Function
LTRIM returns a VARCHAR2 string that is the same as arg1 with the characters in arg2 removed. LTRIM keeps scanning from the left most character and keeps removing characters until none match arg2. Example: SELECT LTRIM(‘xxxx-AA’,’x’) as RESULT from dual;