Oracle Functions
ASCII
Returns the ASCII value (NUMBER) that represents the specified character.
ASCII(SINGLE_STRING)
CHR(84) - 'T'
LOWER
FLOOR
Function returns the largest integer value that is equal to or less than a number.
FLOOR( NUMBER )
FLOOR(5.9) - 5
FLOOR(34.29) - 34
FLOOR(-5.9) - -6
ASCII(SINGLE_STRING)
ASCII('t') - 116
ASCII('T') - 84
ASCII('T2') - 84
ASCIISTR
ASCII('T2') - 84
ASCIISTR
Converts a string in any character set to an ASCII string using the database character set.
ASCIISTR(STRING)
ASCIISTR('A B C Ä Ê') - 'A B C \00C4 \00CA'
ASCIISTR('A B C Õ Ø') - 'A B C \00D5 \00D8'
ASCIISTR('A B C Ä Ê Í Õ Ø') - 'A B C \00C4 \00CA \00CD \00D5\00D8'
CHR
ASCIISTR(STRING)
ASCIISTR('A B C Ä Ê') - 'A B C \00C4 \00CA'
ASCIISTR('A B C Õ Ø') - 'A B C \00D5 \00D8'
ASCIISTR('A B C Ä Ê Í Õ Ø') - 'A B C \00C4 \00CA \00CD \00D5\00D8'
CHR
Returns the character based on the NUMBER code.
This is opposite of the ascii function.
CHR(NUMBER_CODE)
CHR(116) - 't'This is opposite of the ascii function.
CHR(NUMBER_CODE)
CHR(84) - 'T'
CONCAT
Allows you to concatenate two strings together.
CONCAT(STRING1, STRING2)
CONCAT('Tech on',' the Net') - 'Tech on the Net'
CONCAT('a', 'b') - 'ab'
Concat ||
CONCAT(STRING1, STRING2)
CONCAT('Tech on',' the Net') - 'Tech on the Net'
CONCAT('a', 'b') - 'ab'
Concat ||
|| operator allows you to concatenate 2 or more strings together.
STRING1 || STRING2 || STRINGn
'Tech on' || ' the Net' - 'Tech on the Net'
'a' || 'b' || 'c' || 'd' - 'abcd'
CONVERT
STRING1 || STRING2 || STRINGn
'Tech on' || ' the Net' - 'Tech on the Net'
'a' || 'b' || 'c' || 'd' - 'abcd'
CONVERT
Converts a string from one character set to another
CONVERT(STRING1 , CHAR_SET_TO , [CHAR_SET_FROM ] )
CONVERT('A B C D E Ä Ê Í Õ Ø', 'US7ASCII', 'WE8ISO8859P1') - 'A B C D E A E I ? ?'
INITCAP
CONVERT(STRING1 , CHAR_SET_TO , [CHAR_SET_FROM ] )
CONVERT('A B C D E Ä Ê Í Õ Ø', 'US7ASCII', 'WE8ISO8859P1') - 'A B C D E A E I ? ?'
INITCAP
Returns first character in each word to uppercase and the rest to lowercase.
INITCAP(STRING)
INITCAP('tech on the net') - 'Tech On The Net'
INITCAP('GEORGE BURNS') - 'George Burns'
INSTR
INITCAP(STRING)
INITCAP('tech on the net') - 'Tech On The Net'
INITCAP('GEORGE BURNS') - 'George Burns'
INSTR
It returns the location of a substring in a string.
INSTR(STRING1,STRING2 [,STRING_POSITION [,nth_APPEARANCE]])
string1 is the string to search.
string2 is the substring to search for in string1.
start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts backstart_position number of characters from the end of string1 and then searches towards the beginning of string1.
nth_appearance is the nth appearance of string2. This is optional. If omitted, it defaults to 1.
Note: If string2 is not found in string1, then the instr Oracle function will return 0.
INSTR('Tech on the net','e') - 2 the first occurrence of 'e'
INSTR('Tech on the net','e',1,1) - 2 the first occurrence of 'e'
INSTR('Tech on the net','e',1,2) - 11 the second occurrence of 'e'
INSTR('Tech on the net','e',1,3) - 14 the third occurrence of 'e'
INSTR('Tech on the net','e',-3,2) -
LENGTH
INSTR(STRING1,STRING2 [,STRING_POSITION [,nth_APPEARANCE]])
string1 is the string to search.
string2 is the substring to search for in string1.
start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts backstart_position number of characters from the end of string1 and then searches towards the beginning of string1.
nth_appearance is the nth appearance of string2. This is optional. If omitted, it defaults to 1.
Note: If string2 is not found in string1, then the instr Oracle function will return 0.
INSTR('Tech on the net','e') - 2 the first occurrence of 'e'
INSTR('Tech on the net','e',1,1) - 2 the first occurrence of 'e'
INSTR('Tech on the net','e',1,2) - 11 the second occurrence of 'e'
INSTR('Tech on the net','e',1,3) - 14 the third occurrence of 'e'
INSTR('Tech on the net','e',-3,2) -
LENGTH
Returns the length of the specified string.
LENGTH(STRING)
LENGTH(STRING)
string1 is NULL, then the function returns NULL.
LENGTH(NULL) - NULL
LENGTH('') - NULL
LENGTH('Tech on the Net') - 15
LENGTH('Tech on the Net ') - 16
Converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function.
LPAD
LOWER(STRING)
LOWER('Tech on the Net') - 'tech on the net'
LOWER('GEORGE BURNS 123 ') - 'george burns 123 '
Function pads the left-side of a string with a specific set of characters (when string1 is not null).
LPAD( STRING1, PADDED_LENGTH [,PAD_STRING])
string1 is the string to pad characters to (the left-hand side).
padded_length is the number of characters to return. If the padded_length is smaller than the original string, the lpad function will truncate the string to the size of padded_length.
pad_string is optional. This is the string that will be padded to the left-hand side of string1. If this parameter is omitted, the lpad function will pad spaces to the left-side of string1.
LPAD('tech', 7) - ' tech'
LPAD('tech', 2) - 'te'
LPAD('tech', 8, '0') - '0000tech'
LPAD('tech on the net', 15, 'z')- 'tech on the net'
LPAD('tech on the net', 16, 'z')- 'ztech on the net'
LPAD( STRING1, PADDED_LENGTH [,PAD_STRING])
string1 is the string to pad characters to (the left-hand side).
padded_length is the number of characters to return. If the padded_length is smaller than the original string, the lpad function will truncate the string to the size of padded_length.
pad_string is optional. This is the string that will be padded to the left-hand side of string1. If this parameter is omitted, the lpad function will pad spaces to the left-side of string1.
LPAD('tech', 7) - ' tech'
LPAD('tech', 2) - 'te'
LPAD('tech', 8, '0') - '0000tech'
LPAD('tech on the net', 15, 'z')- 'tech on the net'
LPAD('tech on the net', 16, 'z')- 'ztech on the net'
LTRIM
Function removes all specified characters from the left-hand side of a string.
LTRIM( STRING1 [, TRIM_STRING])
string1 is the string to trim the characters from the left-hand side.
trim_string is the string that will be removed from the left-hand side of string1.
If this parameter is omitted, the ltrim function will remove all leading spaces from string1.
LTRIM(' tech') - 'tech'
LTRIM(' tech', ' ') - 'tech'
LTRIM('000123', '0') - '123'
LTRIM('123123Tech', '123') - 'Tech'
LTRIM('123123Tech123', '123') - 'Tech123'
LTRIM('xyxzyyyTech', 'xyz') - 'Tech'
LTRIM('6372Tech', '0123456789') - 'Tech'
The ltrim function may appear to remove patterns, but this is not the case as demonstrated in the following example.
LTRIM ('xyxzyyyTech', 'xyz')
It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.
The ltrim function can also be used to remove all leading numbers as demonstrated in the next example.
LTRIM ('6372Tech', '0123456789')
In this example, every number combination from 0 to 9 has been listed in the trim_string parameter. By doing this, it does not matter the order that the numbers appear in string1, all leading numbers will be removed by the ltrim function.
REPLACE
string1 is the string to trim the characters from the left-hand side.
trim_string is the string that will be removed from the left-hand side of string1.
If this parameter is omitted, the ltrim function will remove all leading spaces from string1.
LTRIM(' tech') - 'tech'
LTRIM(' tech', ' ') - 'tech'
LTRIM('000123', '0') - '123'
LTRIM('123123Tech', '123') - 'Tech'
LTRIM('123123Tech123', '123') - 'Tech123'
LTRIM('xyxzyyyTech', 'xyz') - 'Tech'
LTRIM('6372Tech', '0123456789') - 'Tech'
The ltrim function may appear to remove patterns, but this is not the case as demonstrated in the following example.
LTRIM ('xyxzyyyTech', 'xyz')
It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.
The ltrim function can also be used to remove all leading numbers as demonstrated in the next example.
LTRIM ('6372Tech', '0123456789')
In this example, every number combination from 0 to 9 has been listed in the trim_string parameter. By doing this, it does not matter the order that the numbers appear in string1, all leading numbers will be removed by the ltrim function.
REPLACE
Function replaces a sequence of characters in a string with another set of characters.
REPLACE(STRING1,STRINGTO_REPLACE [,REPLACEMENT_STRING])
string1 is the string to replace a sequence of characters with another set of characters.
string_to_replace is the string that will be searched for in string1.
replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the replace function simply removes all occurrences of string_to_replace, and returns the resulting string.
REPLACE('123123tech', '123') - 'tech'
REPLACE('123tech123', '123') - 'tech'
REPLACE('222tech', '2', '3') - '333tech'
REPLACE('0000123', '0') - '123'
REPLACE('0000123', '0', ' ') - ' 123'
REPLACE ('123tech', '231', '555') - '123tech'
REPLACE(STRING1,STRINGTO_REPLACE [,REPLACEMENT_STRING])
string1 is the string to replace a sequence of characters with another set of characters.
string_to_replace is the string that will be searched for in string1.
replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the replace function simply removes all occurrences of string_to_replace, and returns the resulting string.
REPLACE('123123tech', '123') - 'tech'
REPLACE('123tech123', '123') - 'tech'
REPLACE('222tech', '2', '3') - '333tech'
REPLACE('0000123', '0') - '123'
REPLACE('0000123', '0', ' ') - ' 123'
REPLACE ('123tech', '231', '555') - '123tech'
RPAD
Function pads the right-side of a string with a specific set of characters (when string1 is not null).
RPAD (STRING1, PADDED_LENGTH [,PAD_STRING])
string1 is the string to pad characters to (the right-hand side).
padded_length is the number of characters to return. If the padded_length is smaller than the original string, the rpad function will truncate the string to the size of padded_length.
pad_string is optional. This is the string that will be padded to the right-hand side of string1. If this parameter is omitted, the rpad function will pad spaces to the right-side of string1.
RPAD('tech', 7) - 'tech '
RPAD('tech', 2) - 'te'
RPAD('tech', 8, '0') - 'tech0000'
RPAD('tech on the net', 15, 'z') - 'tech on the net'
RPAD('tech on the net', 16, 'z') - 'tech on the netz'
RTRIM
RPAD (STRING1, PADDED_LENGTH [,PAD_STRING])
string1 is the string to pad characters to (the right-hand side).
padded_length is the number of characters to return. If the padded_length is smaller than the original string, the rpad function will truncate the string to the size of padded_length.
pad_string is optional. This is the string that will be padded to the right-hand side of string1. If this parameter is omitted, the rpad function will pad spaces to the right-side of string1.
RPAD('tech', 7) - 'tech '
RPAD('tech', 2) - 'te'
RPAD('tech', 8, '0') - 'tech0000'
RPAD('tech on the net', 15, 'z') - 'tech on the net'
RPAD('tech on the net', 16, 'z') - 'tech on the netz'
RTRIM
Function removes all specified characters from the right-hand side of a string.
RTRIM( STRING1 [,TRIM_STRING] )
string1 is the string to trim the characters from the right-hand side.
trim_string is the string that will be removed from the right-hand side of string1. If this parameter is omitted, the rtrim function will remove all trailing spaces from string1.
RTRIM( STRING1 [,TRIM_STRING] )
string1 is the string to trim the characters from the right-hand side.
trim_string is the string that will be removed from the right-hand side of string1. If this parameter is omitted, the rtrim function will remove all trailing spaces from string1.
RTRIM('tech ') - 'tech'
RTRIM('tech ', ' ') - 'tech'
RTRIM('123000', '0') - '123'
RTRIM('Tech123123', '123') - 'Tech'
RTRIM('123Tech123', '123') - '123Tech'
RTRIM('Techxyxzyyy', 'xyz') - 'Tech'
RTRIM('Tech6372', '0123456789') - 'Tech'
The rtrim function may appear to remove patterns, but this is not the case as demonstrated in the above example.
RTRIM ('Techxyxzyyy', 'xyz')
It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.
RTRIM ('Tech6372', '0123456789')
The rtrim function can also be used to remove all trailing numbers as demonstrated in the next example.
In this example, every number combination from 0 to 9 has been listed in the trim_string parameter. By doing this, it does not matter the order that the numbers appear in string1, all trailing numbers will be removed by the rtrim function.
SOUNDEX
RTRIM('tech ', ' ') - 'tech'
RTRIM('123000', '0') - '123'
RTRIM('Tech123123', '123') - 'Tech'
RTRIM('123Tech123', '123') - '123Tech'
RTRIM('Techxyxzyyy', 'xyz') - 'Tech'
RTRIM('Tech6372', '0123456789') - 'Tech'
The rtrim function may appear to remove patterns, but this is not the case as demonstrated in the above example.
RTRIM ('Techxyxzyyy', 'xyz')
It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.
RTRIM ('Tech6372', '0123456789')
The rtrim function can also be used to remove all trailing numbers as demonstrated in the next example.
In this example, every number combination from 0 to 9 has been listed in the trim_string parameter. By doing this, it does not matter the order that the numbers appear in string1, all trailing numbers will be removed by the rtrim function.
SOUNDEX
Function returns a phonetic representation (the way it sounds) of a string.
SOUNDEX( STRING )
The Soundex algorithm is as follows:
1. The soundex return value will always begin with the first letter of string1.
2. The soundex function uses only the first 5 consonants to determine the NUMERIC portion of the return value, except if the first letter of string1 is a vowel.
The soundex function is not case-sensitive. What this means is that both uppercase and lowercase characters will generate the same soundex return value.
SOUNDEX('tech on the net') - 'T253'
SOUNDEX('TECH ON THE NET') - 'T253'
SOUNDEX('apples') - 'A142'
SOUNDEX('apples are great') - 'A142'
SOUNDEX('applus') - 'A142'
SUBSTR
SOUNDEX( STRING )
The Soundex algorithm is as follows:
1. The soundex return value will always begin with the first letter of string1.
2. The soundex function uses only the first 5 consonants to determine the NUMERIC portion of the return value, except if the first letter of string1 is a vowel.
The soundex function is not case-sensitive. What this means is that both uppercase and lowercase characters will generate the same soundex return value.
SOUNDEX('tech on the net') - 'T253'
SOUNDEX('TECH ON THE NET') - 'T253'
SOUNDEX('apples') - 'A142'
SOUNDEX('apples are great') - 'A142'
SOUNDEX('applus') - 'A142'
SUBSTR
Function allows you to extract a substring from a string.
SUBSTR (STRING, START_POSITION [,LENGTH])
string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.
Note:
If start_position is 0, then substr treats start_position as 1 (ie: the first position in the string).
If start_position is a positive number, then substr starts from the beginning of the string.
If start_position is a negative number, then substr starts from the end of the string and counts backwards.
If length is a negative number, then substr will return a NULL value.
SUBSTR ('This is a test', 6, 2) - 'is'
string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.
Note:
If start_position is 0, then substr treats start_position as 1 (ie: the first position in the string).
If start_position is a positive number, then substr starts from the beginning of the string.
If start_position is a negative number, then substr starts from the end of the string and counts backwards.
If length is a negative number, then substr will return a NULL value.
SUBSTR ('This is a test', 6, 2) - 'is'
SUBSTR ('This is a test', 6) - 'is a test'
SUBSTR ('TechOnTheNet', 1, 4) - 'Tech'
SUBSTR ('TechOnTheNet', -3, 3) - 'Net'
SUBSTR ('TechOnTheNet', -6, 3) - 'The'
SUBSTR ('TechOnTheNet', -8, 2) - 'On'
SUBSTR ('TechOnTheNet', 7, -2) - null
SUBSTR ('TechOnTheNet', 1, 4) - 'Tech'
SUBSTR ('TechOnTheNet', -3, 3) - 'Net'
SUBSTR ('TechOnTheNet', -6, 3) - 'The'
SUBSTR ('TechOnTheNet', -8, 2) - 'On'
SUBSTR ('TechOnTheNet', 7, -2) - null
TRANSLATE
Translate function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.
TRANSLATE(STRING1,STRING_TO_REPLACE,REPLACEMENT_STRING)
TRANSLATE('1tech23', '123', '456') - '4tech56'
TRANSLATE('222tech', '2ec', '3it') - '333tith'
TRIM
Function removes all specified characters either from the beginning or the ending of a string.
TRIM ([LEADING | TRAILING | BOTH [TRIM_CHARACTER] FROM STRING1)
leading - remove trim_string from the front of string1.
Translate function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.
TRANSLATE(STRING1,STRING_TO_REPLACE,REPLACEMENT_STRING)
TRANSLATE('1tech23', '123', '456') - '4tech56'
TRANSLATE('222tech', '2ec', '3it') - '333tith'
TRIM
Function removes all specified characters either from the beginning or the ending of a string.
TRIM ([LEADING | TRAILING | BOTH [TRIM_CHARACTER] FROM STRING1)
leading - remove trim_string from the front of string1.
trailing - remove trim_string from the end of string1.
both - remove trim_string from the front and end of string1.
If none of these are chosen (ie: leading, trailing, both), the trim function will remove trim_string from both the front and end of string1.
trim_character is the character that will be removed from string1. If this parameter is omitted, the trim function will remove all leading and trailing spaces from string1.
string1 is the string to trim.
TRIM(' tech ') - 'tech'
TRIM(' ' FROM ' tech ') - 'tech'
TRIM(LEADING '0' FROM '000123') - '123'
TRIM(TRAILING '1' FROM 'Tech1') - 'Tech'
TRIM(BOTH '1' FROM '123Tech111') - '23Tech'
UPPER
Converts all letters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.
UPPER (STRING)
UPPER('Tech on the Net') - 'TECH ON THE NET'
UPPER('george burns 123 ') - 'GEORGE BURNS 123 '
TO_CHAR
Function converts a number or date to a string.
TO_CHAR(VALUE [,FORMAT_MASK] [,NLS_LANGUAGE])
value can either be a number or date that will be converted to a string.
both - remove trim_string from the front and end of string1.
If none of these are chosen (ie: leading, trailing, both), the trim function will remove trim_string from both the front and end of string1.
trim_character is the character that will be removed from string1. If this parameter is omitted, the trim function will remove all leading and trailing spaces from string1.
string1 is the string to trim.
TRIM(' tech ') - 'tech'
TRIM(' ' FROM ' tech ') - 'tech'
TRIM(LEADING '0' FROM '000123') - '123'
TRIM(TRAILING '1' FROM 'Tech1') - 'Tech'
TRIM(BOTH '1' FROM '123Tech111') - '23Tech'
UPPER
Converts all letters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.
UPPER (STRING)
UPPER('Tech on the Net') - 'TECH ON THE NET'
UPPER('george burns 123 ') - 'GEORGE BURNS 123 '
TO_CHAR
Function converts a number or date to a string.
TO_CHAR(VALUE [,FORMAT_MASK] [,NLS_LANGUAGE])
value can either be a number or date that will be converted to a string.
format_mask is optional. This is the format that will be used to convert value to a string.
nls_language is optional. This is the nls language used to convert value to a string.
Examples :
Query Result
TO_CHAR(SYSDATE,'DAY') 'MONDAY'
TO_CHAR (SYSDATE, 'Day, ddth Month, yyyy') 'Monday, 30th January, 2012'
TO_CHAR (SYSDATE, 'Day, ddth Month, yyyysp') 'Monday, 30th January, two thousand twelve'
TO_CHAR (SYSDATE, 'Day, ddth Month, yyyyspth') 'Monday, 30th January, two thousand twelfth'
TO_CHAR(1210.73, '9999.9') '1210.7'
TO_CHAR(1210.73, '9,999.99') '1,210.73'
TO_CHAR(1210.73, '$9,999.00') '$1,210.73'
TO_CHAR(21, '000099') '000021'
TO_CHAR(SYSDATE, 'yyyy/mm/dd') '2003/07/09'
TO_CHAR(SYSDATE, 'Month DD, YYYY') 'July 09, 2003'
TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') 'July 9, 2003'
TO_CHAR(SYSDATE, 'MON DDth, YYYY') 'JUL 09TH, 2003'
TO_CHAR(SYSDATE, 'FMMON DDth, YYYY') 'JUL 9TH, 2003'
TO_CHAR(SYSDATE, 'FMMon ddth, YYYY') 'Jul 9th, 2003'
Format Mask :
Format Meaning
D Day of the week (1(sunday) to 7(saturday))
nls_language is optional. This is the nls language used to convert value to a string.
Examples :
Query Result
TO_CHAR(SYSDATE,'DAY') 'MONDAY'
TO_CHAR (SYSDATE, 'Day, ddth Month, yyyy') 'Monday, 30th January, 2012'
TO_CHAR (SYSDATE, 'Day, ddth Month, yyyysp') 'Monday, 30th January, two thousand twelve'
TO_CHAR (SYSDATE, 'Day, ddth Month, yyyyspth') 'Monday, 30th January, two thousand twelfth'
TO_CHAR(1210.73, '9999.9') '1210.7'
TO_CHAR(1210.73, '9,999.99') '1,210.73'
TO_CHAR(1210.73, '$9,999.00') '$1,210.73'
TO_CHAR(21, '000099') '000021'
TO_CHAR(SYSDATE, 'yyyy/mm/dd') '2003/07/09'
TO_CHAR(SYSDATE, 'Month DD, YYYY') 'July 09, 2003'
TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') 'July 9, 2003'
TO_CHAR(SYSDATE, 'MON DDth, YYYY') 'JUL 09TH, 2003'
TO_CHAR(SYSDATE, 'FMMON DDth, YYYY') 'JUL 9TH, 2003'
TO_CHAR(SYSDATE, 'FMMon ddth, YYYY') 'Jul 9th, 2003'
Format Mask :
Format Meaning
D Day of the week (1(sunday) to 7(saturday))
DD Day of the month (1 to 31)
DDD Day of the year (1 to 365)
DDD Day of the year (1 to 365)
DAY Full day for ex. 'Monday', 'Tuesday'.
DY Day in three letters for ex. 'MON', 'TUE'.
W Week of the month (1 to 5)
WW Week of the year (1 to 53)
MM Month in two digits (1-Jan, 2-Feb,…12-Dec)
MON Month in three characters like "Jan", "Feb".
MONTH Full Month like "January", "February".
RM Month in Roman Characters (I-XII, I-Jan, II-Feb,…XII-Dec)
Q Quarter of the Month
YY Last two digits of the year.
YYYY Full year
YEAR Year in words like "Nineteen Ninety Nine"
HH Hours in 12 hour format
HH12 Hours in 12 hour format
HH24 Hours in 24 hour format
MI Minutes
SS Seconds
FF Fractional Seconds
SSSSS Milliseconds
J Julian Day i.e Days since 1st-Jan-4712BC to till-date
RR If the year is less than 50 Assumes the year as 21ST Century.
If the year is greater than 50 then assumes the year in 20th Century.
DY Day in three letters for ex. 'MON', 'TUE'.
W Week of the month (1 to 5)
WW Week of the year (1 to 53)
MM Month in two digits (1-Jan, 2-Feb,…12-Dec)
MON Month in three characters like "Jan", "Feb".
MONTH Full Month like "January", "February".
RM Month in Roman Characters (I-XII, I-Jan, II-Feb,…XII-Dec)
Q Quarter of the Month
YY Last two digits of the year.
YYYY Full year
YEAR Year in words like "Nineteen Ninety Nine"
HH Hours in 12 hour format
HH12 Hours in 12 hour format
HH24 Hours in 24 hour format
MI Minutes
SS Seconds
FF Fractional Seconds
SSSSS Milliseconds
J Julian Day i.e Days since 1st-Jan-4712BC to till-date
RR If the year is less than 50 Assumes the year as 21ST Century.
If the year is greater than 50 then assumes the year in 20th Century.
Suffixes :
TH
Returns th, st, rd or nd according to the leading number like 1st, 2nd, 3rd
SP
Spells out the leading number
AM or PM
Returns AM or PM according to the time
SPTH
Returns Spelled Ordinal number. For. Example First, Fourth
TO_DATE
Function converts a string to a date.
TO_DATE(STRING [,FORMAT_MASK] [,NLS_LANGUAGE])
string1 is the string that will be converted to a date.
TH
Returns th, st, rd or nd according to the leading number like 1st, 2nd, 3rd
SP
Spells out the leading number
AM or PM
Returns AM or PM according to the time
SPTH
Returns Spelled Ordinal number. For. Example First, Fourth
TO_DATE
Function converts a string to a date.
TO_DATE(STRING [,FORMAT_MASK] [,NLS_LANGUAGE])
string1 is the string that will be converted to a date.
format_mask is optional. This is the format that will be used to convert string1 to a date.
nls_language is optional. This is the nls language used to convert string1 to a date.
TO_DATE('2003/07/09','yyyy/mm/dd')- JULY 9, 2003
TO_DATE('070903','MMDDYY') - JULY 9, 2003
TO_DATE('20020315','yyyymmdd') - MAR 15, 2002
By default, date will be displayed in session NLS date format.
TO_NUMBER
Function converts a string to a number.
TO_NUMBER(STRING1 [,FORMAT_MASK] [,NLS_LANGUAGE])
string1 is the string that will be converted to a number.
format_mask is optional. This is the format that will be used to convert string1 to a number.
nls_language is optional. This is the nls language used to convert string1 to a date.
TO_DATE('2003/07/09','yyyy/mm/dd')- JULY 9, 2003
TO_DATE('070903','MMDDYY') - JULY 9, 2003
TO_DATE('20020315','yyyymmdd') - MAR 15, 2002
By default, date will be displayed in session NLS date format.
TO_NUMBER
Function converts a string to a number.
TO_NUMBER(STRING1 [,FORMAT_MASK] [,NLS_LANGUAGE])
string1 is the string that will be converted to a number.
format_mask is optional. This is the format that will be used to convert string1 to a number.
nls_language is optional. This is the nls language used to convert string1 to a number.
TO_NUMBER('1210.73', '9999.99') - 1210.73
TO_NUMBER('546', '999') - 546
TO_NUMBER('23', '99') - 23
Since the format_mask and nls_language parameters are optional, you can simply convert a text string to a numeric
value as follows:
TO_NUMBER('1210.73') - 1210.73
TO_TIMESTAMP
Function converts a string to a timestamp.
TO_TIMESTAMP( STRING1 [,FORMAT_MASK] [,NLSPARAM])
TO_TIMESTAMP('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')
- '13-DEC-03 10.13.18.000000000 AM'
TO_TIMESTAMP('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS')
- '13-DEC-03 10.13.18.000000000 AM'
ABS
Returns absolute value of a number
ABS( NUMBER )
ABS(-23) - 23
ABS(-23.65) - 23.65
ABS(23.65) - 23.65
ABS(23.65 * -1) - 23.65
AVG
Function returns average value of an expression.
AVG( EXPRESSION )
Basic Syntax is :
SELECT Avg( expression )
FROM tables
WHERE predicates;
CEIL
Function returns the smallest integer value that is greater than or equal to a number.
CEIL ( NUMBER )
CEIL(32.65) - 33
CEIL(32) - 32
TO_NUMBER('1210.73', '9999.99') - 1210.73
TO_NUMBER('546', '999') - 546
TO_NUMBER('23', '99') - 23
Since the format_mask and nls_language parameters are optional, you can simply convert a text string to a numeric
value as follows:
TO_NUMBER('1210.73') - 1210.73
TO_TIMESTAMP
Function converts a string to a timestamp.
TO_TIMESTAMP( STRING1 [,FORMAT_MASK] [,NLSPARAM])
TO_TIMESTAMP('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')
- '13-DEC-03 10.13.18.000000000 AM'
TO_TIMESTAMP('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS')
- '13-DEC-03 10.13.18.000000000 AM'
ABS
Returns absolute value of a number
ABS( NUMBER )
ABS(-23) - 23
ABS(-23.65) - 23.65
ABS(23.65) - 23.65
ABS(23.65 * -1) - 23.65
AVG
Function returns average value of an expression.
AVG( EXPRESSION )
Basic Syntax is :
SELECT Avg( expression )
FROM tables
WHERE predicates;
CEIL
Function returns the smallest integer value that is greater than or equal to a number.
CEIL ( NUMBER )
CEIL(32.65) - 33
CEIL(32) - 32
CEIL(-32.65) - -32
CEIL(-32) - -32
CEIL(-32) - -32
COUNT
Function returns number of rows in a query.
COUNT ( EXPRESSION )
Supplier_ID Supplier_Name State
1 IBM CA
2 Microsoft
3 NVIDIA
COUNT(*) - 3
COUNT(1) - 3
COUNT(SUPPLIER_ID) - 3
Function returns number of rows in a query.
COUNT ( EXPRESSION )
Supplier_ID Supplier_Name State
1 IBM CA
2 Microsoft
3 NVIDIA
COUNT(*) - 3
COUNT(1) - 3
COUNT(SUPPLIER_ID) - 3
COUNT(STATE) - 1
FLOOR
Function returns the largest integer value that is equal to or less than a number.
FLOOR( NUMBER )
FLOOR(5.9) - 5
FLOOR(34.29) - 34
FLOOR(-5.9) - -6
GREATEST
Function returns the greatest value in a list of expressions.
GREATEST( EXPR1, EXPR2, … EXPR_n )
expr1, expr2, . expr_n are expressions that are evaluated by the greatest function.
If the datatypes of the expressions are different, all expressions will be converted to whatever datatype expr1 is.
If the comparison is based on a character comparison, one character is considered greater than another if it has a higher character set value.
GREATEST(2, 5, 12, 3) - 12
GREATEST('2', '5', '12', '3') - '5'
GREATEST('apples', 'oranges', 'bananas') - 'oranges'
GREATEST('apples', 'applis', 'applas') - 'applis'
Note :
Having a NULL value in one of the expressions will return NULL as the greatest value.
LEAST
Function returns the smallest value in a list of expressions.
LEAST( EXPR1, EXPR2, … EXPR_n )
expr1, expr2, . expr_n are expressions that are evaluated by the least function.
If the datatypes of the expressions are different, all expressions will be converted to whatever datatype expr1 is.
If the comparison is based on a character comparison, one character is considered smaller than another if it has a lower character set value.
Note :
Function returns the greatest value in a list of expressions.
GREATEST( EXPR1, EXPR2, … EXPR_n )
expr1, expr2, . expr_n are expressions that are evaluated by the greatest function.
If the datatypes of the expressions are different, all expressions will be converted to whatever datatype expr1 is.
If the comparison is based on a character comparison, one character is considered greater than another if it has a higher character set value.
GREATEST(2, 5, 12, 3) - 12
GREATEST('2', '5', '12', '3') - '5'
GREATEST('apples', 'oranges', 'bananas') - 'oranges'
GREATEST('apples', 'applis', 'applas') - 'applis'
Note :
Having a NULL value in one of the expressions will return NULL as the greatest value.
LEAST
Function returns the smallest value in a list of expressions.
LEAST( EXPR1, EXPR2, … EXPR_n )
expr1, expr2, . expr_n are expressions that are evaluated by the least function.
If the datatypes of the expressions are different, all expressions will be converted to whatever datatype expr1 is.
If the comparison is based on a character comparison, one character is considered smaller than another if it has a lower character set value.
Note :
Having a NULL value in one of the expressions will return NULL as the least value.
LEAST(2, 5, 12, 3) - 2
LEAST('2', '5', '12', '3') - '12'
LEAST('apples', 'oranges', 'bananas') - 'apples'
LEAST('apples', 'applis', 'applas') - 'applas'
LEAST('apples', 'applis', 'applas', NULL) - NULL
LEAST(2, 5, 12, 3) - 2
LEAST('2', '5', '12', '3') - '12'
LEAST('apples', 'oranges', 'bananas') - 'apples'
LEAST('apples', 'applis', 'applas') - 'applas'
LEAST('apples', 'applis', 'applas', NULL) - NULL
0 comments:
Post a Comment