EXTRACT allows you to extract parts of a date expression. You may want to extract the year from a date string, or minute value in a date-time expression. Valid extractions include YEAR, MONTH, DAY,HOUR, MINUTE,SECOND,TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION and TIMEZONE_ABBR Example:
Concat – Oracle SQL Function
CONCAT returns 1st argument concatenated with 2nd argument. The arguments can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB. If the two arguments are of the same data type, then CONCAT returns a string of that type. If they are
FROM_TZ – Oracle SQL Function
FROM_TZ converts a timestamp and time zone value into a TIMESTAMP WITH TIMEZONE value. Syntax: FROM_TZ(timestamp, timezone) Example: SELECT FROM_TZ(TIMESTAMP ‘2004-10-24 06:00:00’, ‘2:00’) as RESULT FROM DUAL; RESULT ————– 24-Oct-04 06.00.00 AM +2:00
Length – Oracle SQL Function
LENGTH returns an integer that is the length of the 1st argument. The 1st argument can be a CHAR, VARCHAR2,NCHAR,NVARCHAR2,CLOB or NCLOB datatype. Example: SELECT LENGTH(‘How long is this?’) as RESULT FROM DUAL; RESULT —————- 17
LAST_DAY – Oracle SQL Function
LAST_DAY returns the last day of the month specified in argument1. Argument1 can be a DATE value or a string that can resolve to a DATE datatype. Example: SELECT LAST_DAY(’12-OCT-04′) as RESULT from DUAL; RESULT ————— 31-OCT-04
Lower – Oracle SQL Function
LOWER returns a string that is the same as the 1st argument with all the characters in lowercase. The argument to LOWER can be a CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB. Example: SELECT LOWER(‘Oracle Rocks’) as RESULT FROM DUAL;
LOCALTIMESTAMP – Oracle SQL Function
LOCALTIMESTAMP returns a TIMESTAMP value. You can specify an optional integer argument to specfiy the precision of the second field. Example: SELECT LOCALTIMESTAMP FROM DUAL; LOCALTIMESTAMP —————————- 13-SEP-05 03.02.49.407000 PM
Lpad – Oracle SQL Function
LPAD returns a VARCHAR2 string that is arg1 left padded with arg3 for a length of n. If arg3 is not provided Oracle will pad with blank spaces. Example: SELECT LPAD(’10 + signs’,10,’+’) as RESULT from DUAL; RESULT —————— ++++++++++10
MONTHS_BETWEEN – Oracle SQL Function
MONTHS_BETWEEN returns an integer specfiying the number of months between two dates. The result can be either positive or negative depending on which date is greater. Example: SELECT MONTHS_BETWEEN(10-JAN-2036′, ’10-JAN-2004′ ) AS RESULT FROM DUAL; RESULT ——————- 384
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;