Character / String Functions:Ascii Convert Lower SoundexAsciiStr Decompose Lpad SubstrChr Dump Ltrim TranslateCompose Initcap Replace TrimConcat Instr Rpad UpperConcat with || Length Rtrim VSize

Conversion Functions:Bin_To_Num NumToDSInterval To_Date To_NumberC ast NumToYMInterval To_DSInterval To_Single_ByteCharToRowid RawToHex To_Lob To_TimestampFrom_Tz To_Char To_Multi_Byte To_Timestamp_TzHexToRaw To_Clob To_NClob To_YMInterval

Advanced Functions:BFilename Group_ID NULLIF UserCardinality Lag NVL UserEnvCase Statement Lead NVL2  Coalesce LNNVL Sys_Context  Decode NANVL Uid  

Mathematical Functions:Abs Covar_pop Max SqrtAcos Covar_samp Median StdDevAsin Count Min SumAtan Cume_Dist Mod TanAtan2 Dense_Rank Power TanhAvg Exp Rank Trunc (numbers)Bin_To_Num Extract Remainder Trunc (dates)BitAnd Floor Round (numbers) Var_popCeil Greatest Round (dates) Var_sampCorr Least Sign VarianceCos Ln Sin  Cosh Log Sinh  

Date Functions:Add_Months Last_Day Round To_DateCurrent_Date LocalTimestamp SessionTimeZone TruncCurrent_Timestamp Months_Between Sysdate Tz_OffsetDbTimeZone New_Time SysTimestampFrom_Tz Next_Day To_Char

Error Functions:SQLCODESQLERRM

Oracle pl/sql trim function

The trim function removed characters from beginning and/or end of a string in Oracle. Oracle has 3 functions for this:

TRIMThe TRIM function trims specified characters from the left and/or right.If no characters are specified, the left and right spaces are left out.Example: trim(' Oradev dot com ') = 'Oradev dot com'.Another option is:trim(trailing 'a' from 'aaaabbaaaa') which results in 'aaaabb' ortrim(leading 'a' from 'aaaabbaaaa') which results in 'bbaaaa' ortrim(both 'a' from 'aaaabbaaaa') which results in 'bb'.

LTRIM LTRIM removes characters from the left of a string if they are equal to the specified string. Example: ltrim('aaaaaabc','a') = 'bc' If the last parameter is not specified, spaces are removed from the left side.

RTRIM

RTRIM removes characters from the right of a string if they are equal to the specified string. Example: rtrim('bcaaaaaa','a') = 'bc' If the last parameter is not specified, spaces are removed from the right side.

Oracle date functions

Oracle has a number of functions that apply to a date Sysdate Returns the current date/time

ADD_MONTHS

Function to add a number of months to a date. For example: add_months(SYSDATE,3) returns 3 months after sysdate. This could be rounded to below is the resulting month has fewer days than the month this function is applied to.

+,- (plus/minus) In Oracle you can add or substract a number of days from a date. Example: sysdate+5 means systemdate/time plus 5 days

GREATEST With the greatest function you can select the date/time that is the highest in a range of date/times. Example: greatest (sysdate+4,sysdate,sysdate-5) = sysdate+4.

LEAST With the least function you can select the earliest date/time in a range of date/times. Example: least(sysdate+4,sysdate,sysdate-5) = sysdate-5.

LAST_DAY Returns the last_day of a month based on the month the passed date is in. Example: last_day(sysdate) returns the last day of this month.

MONTHS_BETWEEN

Returns the number of months between two dates. The number is not rounded. Example: months_between(sysdate, to_date('01-01-2007','dd-mm-yyyy')) returns the number of months since jan 1, 2007.

NEXT_DAY

Date of next specified date following a date NEXT_DAY(, ) Options are SUN, MON, TUE, WED, THU, FRI, and SAT SELECT NEXT_DAY(SYSDATE, 'FRI') FROM dual; NOTE: This can be dependend on NLS_SETTINGS!

ROUND

Returns date rounded to the unit specified by the format model. If you omit the format, the date is rounded to the nearest day ROUND(, ) SELECT ROUND(TO_DATE('27-OCT-00'),'YEAR') NEW_YEAR FROM dual;

TRUNC Convert a date to the date without time (0:00h) Example: TRUNC(sysdate) returns today without time.

First day of the month. trunc(example_date,'MM') Example: select trunc(TO_DATE('31-JAN-2007'),'MM') FROM dual;

TO_CHAR(date,format_mask) Converts a date to a string using a format mask. Format masks are explained here

Single row numeric functions

Oracle provides a lot of standard numeric functions for single rows. Here is a list of all the single row numeric functions (in version 10.2).

Function Usage Description

ABS ABS(n) ABS returns the absolute value of n.

ACOS ACOS(n)

ACOS returns the arc cosine of n. The argument n must be in the range of -1 to 1, and the function returns a value in the range of 0 to pi, expressed in radians.

ASIN ASIN(n) ASIN returns the arc sine of n. The argument n must be in the range of -1 to 1, and the function returns a value

in the range of -pi/2 to pi/2, expressed in radians.

ATAN ATAN(n)

ATAN returns the arc tangent of n. The argument n can be in an unbounded range and returns a value in the range of -pi/2 to pi/2, expressed in radians.

ATAN2 ATAN2(n1,n2) or ATAN2(n1/n2)

ATAN2 returns the arc tangent of n1 and n2. The argument n1 can be in an unbounded range and returns a value in the range of -pi to pi, depending on the signs of n1 and n2, expressed in radians. ATAN2(n1,n2) is the same as ATAN2(n1/n2).

BITAND BITAND(expr1,expr2)

BITAND computes an AND operation on the bits of expr1 and expr2, both of which must resolve to nonnegative integers, and returns an integer. This function is commonly used with the DECODE function, as illustrated in the example that follows.

CEIL CEIL(n)

This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.

COS COS(n)

COS returns the cosine of n (an angle expressed in radians). This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise the function returns the same numeric datatype as the argument.

COSH COSH(n) COSH returns the hyperbolic cosine of n. This function takes as an argument any numeric datatype or

any nonnumeric datatype that can be implicitly converted to a numeric datatype. If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise the function returns the same numeric datatype as the argument.

EXP EXP(n)

EXP returns e raised to the nth power, where e = 2.71828183... The function returns a value of the same type as the argument. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise the function returns the same numeric datatype as the argument.

FLOOR FLOOR(n)

FLOOR returns largest integer equal to or less than n. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.

LN LN(n)

LN returns the natural logarithm of n, where n is greater than 0. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise the function returns the same numeric datatype as the argument.

LOG LOG(n2,n1) LOG returns the logarithm, base n2, of n1. The base n1 can be any positive value other than 0 or 1 and n2 can be any positive value. This

function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If any argument is BINARY_FLOAT or BINARY_DOUBLE, then the function returns BINARY_DOUBLE. Otherwise the function returns NUMBER.

MOD MOD(n2,n1)

MOD returns the remainder of n2 divided by n1. Returns n2 if n1 is 0. This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

NANVL NANVL(n2,n1)

The NANVL function is useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE. It instructs Oracle Database to return an alternative value n1 if the input value n2 is NaN (not a number). If n2 is not NaN, then Oracle returns n2. This function is useful for mapping NaN values to NULL. This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

POWER POWER(n2,n1) POWER returns n2 raised to the n1 power. The base n2 and the exponent n1 can be any numbers, but if n2 is negative, then n1 must be an integer. This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly

converted to a numeric datatype. If any argument is BINARY_FLOAT or BINARY_DOUBLE, then the function returns BINARY_DOUBLE. Otherwise the function returns NUMBER.

REMAINDER REMAINDER(n2,n1)

REMAINDER returns the remainder of n2 divided by n1. This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype. The MOD function is similar to REMAINDER except that it uses FLOOR in its formula, whereas REMAINDER uses ROUND.

ROUND (number) ROUND (n) ROUND(n, integer)

ROUND returns n rounded to integer places to the right of the decimal point. If you omit integer, then n is rounded to 0 places. The argument integer can be negative to round off digits left of the decimal point. n can be any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The argument integer must be an integer. If you omit integer, then the function returns the same datatype as the numeric datatype of the argument. If you include integer, then the function returns NUMBER. For NUMBER values, the value n is rounded away from 0 (for example, to x+1 when x.5 is positive and to x-1 when x.5 is negative). For BINARY_FLOAT and BINARY_DOUBLE values, the function rounds to the nearest even value. Please refer to the examples that follow.

SIGN SIGN(n)

SIGN returns the sign of n. This function takes as an argument any numeric datatype, or any nonnumeric datatype that can be implicitly converted to NUMBER, and returns NUMBER. of NUMBER type, the sign is:-1 if n<00 if n=01 if n>0

For binary floating-point numbers (BINARY_FLOAT and BINARY_DOUBLE), this function returns the sign bit of the number. The sign bit is:-1 if n<0+1 if n>=0 or n=NaN

SIN SIN(n)

SIN returns the sine of n (an angle expressed in radians). This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise the function returns the same numeric datatype as the argument.

SINH SINH(n)

SINH returns the hyperbolic sine of n. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise the function returns the same numeric datatype as the argument.

SQRT SQRT(n) SQRT returns the square root of n. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric

datatype. The function returns the same datatype as the numeric datatype of the argument.

TAN TAN(n)

TAN returns the tangent of n (an angle expressed in radians). This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise the function returns the same numeric datatype as the argument.

TANH TANH(n)

TANH returns the hyperbolic tangent of n. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise the function returns the same numeric datatype as the argument.

TRUNC (number) TRUNC(n1) TRUNC(n1,n2)

The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If you omit n2, then the function returns the same datatype as the numeric datatype of the argument. If you include n2, then the function returns NUMBER.

WIDTH_BUCKET WIDTH_BUCKET(expr,min_value, max_value,num_buckets)

WIDTH_BUCKET lets you construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size.

(Compare this function with NTILE, which creates equiheight histograms.) Ideally each bucket is a closed-open interval of the real number line. For example, a bucket can be assigned to scores between 10.00 and 19.999... to indicate that 10 is included in the interval and 20 is excluded. This is sometimes denoted [10, 20).For a given expression, WIDTH_BUCKET returns the bucket number into which the value of this expression would fall after being evaluated.expr is the expression for which the histogram is being created. This expression must evaluate to a numeric or datetime value or to a value that can be implicitly converted to a numeric or datetime value. If expr evaluates to null, then the expression returns null.min_value and max_value are expressions that resolve to the end points of the acceptable range for expr. Both of these expressions must also evaluate to numeric or datetime values, and neither can evaluate to null.num_buckets is an expression that resolves to a constant indicating the number of buckets. This expression must evaluate to a positive integer.

Oracle string functions

ASCII The ASCII function returns the decimal representation in the database character set of the first character of char. Example: ASCII('b') =98

CHR The CHR function returns the character having the binary equivalent to n as a VARCHAR2 value in either the database character set. Example: CHR(10)||CHR(13) = carriage return plus line feed.

COALESCE

The COALESCE function returns the first non-null expr in the expression list. At least one expr must not be the literal NULL. If all occurrences of expr evaluate to null, then the function returns null. Example: select COALESCE(col1, col2, col3) FROM emp;

CONCAT The CONCAT function returns the concatenation of 2 strings. You can also use the || command for this. Example: CONCAT('abc','def') = 'abcdef'

CONVERT

The CONVERT function converts a string from one characterset to another. The datatype of the returned value is VARCHAR2. Example: CONVERT('This is an example','UTF-8','WE8ISO8859P1') SELECT CONVERT('Ä Ê Í Ó Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL; = A E I ? ? A B C D E ?

DUMP The DUMP function returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The returned result is always in the database character set.

INSTR Returns the position of a String within a String. For more information see Oracle instr function

INITCAP Transform String to init cap Example: INITCAP('ORADEV') = 'Oradev' INSTRB Returns the position of a String within a String, expressed in bytes.

INSTRC Returns the position of a String within a String, expressed in Unicode complete characters

INSTR2 Returns the position of a String within a String, expressed in UCS2 code points INSTR4 Returns the position of a String within a String, expressed in UCS4 code points

LENGTH The LENGTH functions returns the length of char. LENGTH calculates length using characters as defined by the input character set. Example: length('oradev.com') = 10

LENGTHB Returns the length of a string, expressed in bytes.

LOWER The LOWER function returns a string with all lower case characters. Example: LOWER('ORADEV') = 'oradev'

LPAD Add characters to the left of a string until a fixed number is reached. Example: lpad('abc',8,'x') = 'xxxxxabc'. If the last parameter is not specified, spaces are added to the left.

LTRIM LTRIM removed characters from the left of a string if they are equal to the specified string. Example: ltrim('aaaaaabc','a') = 'bc' If the last parameter is not specified, spaces are removed from the left side.

REPLACE The replace function replaces every occurrence of a search_string with a new string. If no new string is specified, all occurrences of the search_string are removed. Example: replace('a1a1a1','a','2') = '212121'.

REVERSE Reverses the characters of a String. Example: REVERSE('oradev.com') = 'moc.vedaro'

RPAD Add characters to the right of a string until a fixed number is reached. Example: rpad('abc',8,'x') = 'abcxxxxx'. If the last parameter is not specified, spaces are added to the right.

RTRIM RTRIM removed characters from the right of a string if they are equal to the specified string. Example: rtrim('bcaaaaaa','a') = 'bc' If the last parameter is not specified, spaces are removed from the right side.

SOUNDEX

SOUNDEX returns a character string containing the phonetic representation of char. This function lets you compare words that are spelled differently, but sound alike in English. Example: select * from emp where lastname SOUNDEX('SMITH');

SUBSTR Returns a substring. For more information see Oracle substring SUBSTRB Returns a substring expressed in bytes instead of characters. SUBSTRC Returns a substring expressed in Unicode code points instead of characters. SUBSTR2 Returns a substring using USC2 code points. SUBSTR4 Returns a substring using USC4 code points.

TRANSLATE

TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in expr that are not in from_string are not replaced. Example: SELECT TRANSLATE('SQL*Plus User''s Guide', ' */''', '___') FROM DUAL; = 'SQL_Plus_Users_Guide'

TRIM The TRIM function trims specified characters from the left and/or right. If no characters are specified, the left and right spaces are left out. Example: trim(' Oradev dot com ') = 'Oradev dot com'.

|| (pipes) With pipes you can concattenate strings. Example 'Oradev'||'.com' = 'Oradev.com'.

UPPER Transform a string to all upper case characters. Example: UPPER('oradev') = 'ORADEV'

VSIZE The VSIZE function returns the byte size of a String.

Oracle INSTR function

The Oracle function instr returns an integer indicating the position of the character in string that is the first character of this occurrence.

This function has the following syntax:

instr(string, substring [,position [,occurrence]])with: string: the string that is searched.substring: the substring which we are looking for in the stringposition: The position from which we start the search (an integer value). If position is negative, then Oracle counts and searches backward from the end of string. If omitted, this defaults to 1.occurrence is an integer indicating which occurrence of string Oracle should search for. The value of occurrence must be positive. If this is omitted, this defaults to 1.Both string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The value returned is of NUMBER datatypeIf no value is found, the instr will return the value 0.

Examples

INSTR('CORPORATE FLOOR','OR', 3, 2) = 14INSTR('CORPORATE FLOOR','OR', -3, 2) = 2INSTR('ab ab ab','ab') = 1INSTR('ab ab ab','ab',1,2) = 4INSTR('ab ab ab','ab',2,2) = 7INSTR('abcabcabcdef','de') = 7

Oracle number format

You can use a number format in Oracle in :1. The TO_CHAR function to format a number datatype.i.e. TO_CHAR(value,'90.99')2. The TO_NUMBER function to convert a CHAR or VARCHAR2 value to a NUMBER datatype.i.e. TO_CHAR('24.33','99.99')

All number format models cause the number to be rounded to the specified number of significant digits. If a value has more significant digits to the left of the decimal place than are specified in the format, then pound signs (#) replace the value. If a positive value is extremely large and cannot be represented in the specified format, then the infinity sign (~) replaces the value. Likewise, if a negative value is extremely small and cannot be represented by the specified format, then the negative infinity sign replaces the value (-~).

Element Example Description

, (comma)

9,999

Returns a comma in the specified position. You can specify multiple commas in a number format model.

Restrictions:

A comma element cannot begin a number format model.

A comma cannot appear to the right of a decimal character or period in a number format model.

. (period) 99.99Returns a decimal point, which is a period (.) in the specified position.

Restriction: You can specify only one period in a number format model.

$ $9999 Returns value with a leading dollar sign.

0 0999 Returns leading zeros.

Element Example Description

9990Returns trailing zeros.

9 9999

Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative.

Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.

B B9999 Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of "0"s in the format model).

C C999 Returns in the specified position the ISO currency symbol (the current value of the NLS_ISO_CURRENCY parameter).

D 99D99

Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period (.).

Restriction: You can specify only one decimal character in a number format model.

EEEE 9.9EEEE Returns a value using in scientific notation.

FM FM90.9 Returns a value with no leading or trailing blanks.

G 9G999

Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER parameter). You can specify multiple group separators in a number format model.

Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model.

L L999 Returns in the specified position the local currency symbol (the current value of the NLS_CURRENCY parameter).

MI 9999MI

Returns negative value with a trailing minus sign (-).

Returns positive value with a trailing blank.

Restriction: The MI format element can appear only in the last position of a number format model.

PR 9999PR

Returns negative value in <angle brackets>.

Returns positive value with a leading and trailing blank.

Restriction: The PR format element can appear only in the last position of a number format model.

RN RN Returns a value as Roman numerals in uppercase.

Element Example Description

rnrn Returns a value as Roman numerals in lowercase.

Value can be an integer between 1 and 3999.

SS9999

9999S

Returns negative value with a leading minus sign (-).

Returns positive value with a leading plus sign (+).

Returns negative value with a trailing minus sign (-).

Returns positive value with a trailing plus sign (+).

Restriction: The S format element can appear only in the first or last position of a number format model.

TM TM

"Text minimum". Returns (in decimal output) the smallest number of characters possible. This element is case-insensitive.

The default is TM9, which returns the number in fixed notation unless the output exceeds 64 characters. If output exceeds 64 characters, then Oracle automatically returns the number in scientific notation.

Restrictions:

You cannot precede this element with any other element.

You can follow this element only with 9 or E (only one) or e (only one).

U U9999 Returns in the specified position the "Euro" (or other) dual currency symbol (the current value of the NLS_DUAL_CURRENCY parameter).

V 999V99 Returns a value multiplied by 10n (and if necessary, round it up), where n is the number of 9's after the "V".

X XXXX

xxxx

Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then Oracle rounds it to an integer.

Restrictions:

This element accepts only positive values or 0. Negative values return an error.

You can precede this element only with 0 (which returns leading zeroes) or FM. Any other elements return an error. If you specify

Element Example Description

neither 0 nor FM with X, then the return always has 1 leading blank.

Oracle to_date function

The oracle to_date function converts a string in a specified format to an Oracle date format.

Syntaxto_date('formatted string'); (returns a date using the default oracle date format)to_date('formatted string','format string'); (returns a date using the format string specified)to_date('formatted string','format string','nls description');(returns a date using the format string specified and using the specified NLS settings)

Example:to_date('01-JAN-2006');to_date('01-01-2004','DD-MM-YYYY');to_date('31-12-2006 23:34:59','DD-MM-YYYY HH24:MI:SS'); to_date('01-JAN-99''DD-MON-YY,'nls_date_language = American');

Oracle date format

With the functions to_char and to_date, a date format can be used. Example:select to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') from dual;will return something like: 24/03/2006 14:36:43

Here is a list of all the formats that can be used:

Format mask

Description

CC Century

SCC Century BC prefixed with -

YYYY Year with 4 numbers

SYYY Year BC prefixed with -

IYYY ISO Year with 4 numbers

YY Year with 2 numbers

RR Year with 2 numbers with Y2k compatibility

YEAR Year in characters

SYEAR Year in characters, BC prefixed with -

BC BC/AD Indicator *

Q Quarter in numbers (1,2,3,4)

MM Month of year 01, 02...12

MONTH Month in characters (i.e. January)

MON JAN, FEB

WW Weeknumber (i.e. 1)

W Weeknumber of the month (i.e. 5)

IW Weeknumber of the year in ISO standard.

DDD Day of year in numbers (i.e. 365)

DD Day of the month in numbers (i.e. 28)

D Day of week in numbers(i.e. 7)

DAY Day of the week in characters (i.e. Monday)

FMDAY Day of the week in characters (i.e. Monday)

DY Day of the week in short character description (i.e. SUN)

J Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle)

HH Hournumber of the day (1-12)

HH12 Hournumber of the day (1-12)

HH24 Hournumber of the day with 24Hours notation (0-23)

AM AM or PM

PM AM or PM

MI Number of minutes (i.e. 59)

SS Number of seconds (i.e. 59)

SSSSS Number of seconds this day.

DS Short date format. Depends on NLS-settings. Use only with timestamp.

DL Long date format. Depends on NLS-settings. Use only with timestamp.

E Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only)

EE The full era name

FF The fractional seconds. Use with timestamp.

FF1..FF9 The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds.

FM Fill Mode: suppresses blianks in output from conversion

FX Format Exact: requires exact pattern matching between data and format model.

IYY or IY or I

the last 3,2,1 digits of the ISO standard year. Output only

RM The Roman numeral representation of the month (I .. XII)

RR The last 2 digits of the year.

RRRRThe last 2 digits of the year when used for output. Accepts fout-digit years when used for input.

SCC Century. BC dates are prefixed with a minus.

CC Century

SP Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten"

SPTH Spelled and ordinal format; 1 results in first.

TH Converts a number to it's ordinal format. For example 1 becoms 1st.

TS Short time format. Depends on NLS-settings. Use only with timestamp.

TZD Abbreviated time zone name. ie PST.

TZH Time zone hour displacement.

TZM Time zone minute displacement.

TZR Time zone region

X Local radix character. In america this is a period (.)

The Oracle substr function

The substr function is a function that returns a substring from a string.

syntaxsubstr([input],[start],[length]) or substr([input],[start]) or With input the String to take a substring from,start is the starting position where 1 is the first character. (if you pass 0, this will be substituted by 1) and

the optional length parameter is the number of characters in the substring. If length is left out, then substr will return the substring from position start till the end of the input-string.

Sample code:select substr('1234567890',3,2) from dual; will return: '34'.

select substr('1234567890',7) from dual; will return: '789'