IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
Hi Folks,
Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format of their arguments. This format enables them to operate on zero, one, two, or more arguments. SQL functions are built into Oracle Database and are available for use in various appropriate SQL statements.
Character Functions Returning Character Values
11 most common character functions
| SYNTAX | DESCRIPTION |
| LOWER(char) | LOWER returns char, with all letters lowercase. |
| UPPER(char) | UPPER returns char, with all letters uppercase. |
| LPAD(expr1, n, expr2) | LPAD returns expr1, left-padded to length n characters with the sequence of characters in expr2. |
| RPAD(expr1, n, expr2) | RPAD returns expr1, right-padded to length n characters with expr2, replicated as many times as necessary. |
| LTRIM(char) | LTRIM removes from the left end of char all of the characters contained in set. If you do not specify set, it defaults to a single blank. |
| RTRIM(char) | RTRIM removes from the right end of char all of the characters that appear in set. If you do not specify set, it defaults to a single blank. |
| TRIM(char) | TRIM removes from both sides of char all the characters that appear in set. If you do not specify set, it defaults to a sigle blank |
| REPLACE(char, search_strin, replacement_string) | REPLACE returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed. |
| SUBSTR(char, position, substring_length) | The SUBSTR functions return a portion of char, beginning at character position, substring_length characters long. |
| TRANSLATE(expr, from_string, to_string) | TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string. |
SELECT LOWER('ABC') "LOWER",
UPPER('abc') "UPPER",
LPAD('1',5,'0') "LPAD",
RPAD('1',5,'0') "RPAD",
LTRIM(' XXX') "LTRIM",
RTRIM('XXX ') "RTRIM",
TRIM(' XX ') "TRIM",
REPLACE('ABC','C','X') "REPLACE",
SUBSTR('abcd', 1, 2) "SUBSTR",
TRANSLATE('A*B', '*', 'X') "TRANSLATE"
FROM DUAL;
A couple of more functions that you might use
| SYNTAX | DESCRIPTION |
| CHR(n) | CHR returns the character having the binary equivalent to n as a VARCHAR2 value in either the database character set or, if you specify USING NCHAR_CS, the national character set. |
| CONTCAT(char1, char2) | CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. |
| INITCAP(char) | INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric. |
| REGEXP_REPLACE(source, pattern, replace) | REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern. |
| REGEXP_SUBSTR(source, pattern, position) | REGEXP_SUBSTR extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern. It is also similar to REGEXP_INSTR, but instead of returning the position of the substring, it returns the substring itself. |
| SOUNDEX(char) | SOUNDEX returns a character string containing the phonetic representation of char. |
SELECT CHR(67) "CHR",
CONCAT('ABC-', 'D') "CONCAT",
INITCAP('hello') "INITCAP",
REGEXP_REPLACE('Brazil', '(.)', '\1 ') "REGEXP_REPLACE",
REGEXP_SUBSTR('http://www.oracle.com/products', 'http://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR",
SOUNDEX('Oracle') "SOUNDEX"
FROM DUAL;
Character Functions Returning Number Values
Character functions that return number values can take as their argument any character datatype.
The character functions that return number values are:
| SYNTAX | DESCRIPTION |
| ASCII(char) | ASCII returns the decimal representation in the database character set of the first character of char. |
| INSTR(string, substring) | The INSTR functions search string for substring. |
| LENGTH(char) | The LENGTH functions return the length of char. |
| REGEXP_INSTR(source, pattern, position) | REGEXP_INSTR extends the functionality of the INSTR function by letting you search a string for a regular expression pattern. |
SELECT ASCII('a') "ASCII",
INSTR('Hello Word', 'W') "INSTR",
LENGTH('Hello') "LENGTH",
REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[^ ]+', 1, 6) "REGEXP_INSTR"
FROM DUAL;
Please write down in the comments any further questions you may have. I’ll be happy to help you.
