Character Functions in oracle
String function plays important role in oracle as often it is required to convert the string or extract certain part of it. These functions accept column value or an expression as the input.
ASCII
This function is used to get the ASCII value of a character.
E.g. SELECT ASCII (’A') from dual;
—returns output as 65
UPPER
This function is used to convert the character string to its upper case.
E.g. SELECT UPPER (’oracle’) FROM dual;
—returns output as ORACLE
LOWER
This function is used to convert the character string to its lower case.
E.g. SELECT LOWER (’ORACLE’) FROM dual;
—returns output as oracle
INITCAP
This function is used to convert the initial letter of the character string to its upper case.
E.g. SELECT INITCAP (’oracle’) FROM dual;
–returns output as Oracle
NLS_UPPER
This function is same as UPPER function except it can use a different sot method defined by NLSSORT.
NLS_LOWER
This function is same as LOWER function except it can use a different sot method defined by NLSSORT
NLS_INITCAP
This function is same as INITCAP function except it can use a different sot method defined by NLSSORT
CHR
The Character function is used to convert the character to the ASCII values.
E.g. SELECT (CHR(65) || CHR(66) || CHR(67)) FROM dual;
—returns output as ABC
CONCAT
This function concatenates the two strings and returns the appended string.
E.g. SELECT CONCAT(’Oracle ‘, ‘Functions’) FROM dual;
—returns output as Oracle Functions
COALESCE
This function returns first non null occurrence. If the occurrence results in NULL then the function returns Null.
E.g. CREATE TABLE SSHARMA.TEST (
col1 VARCHAR2(1),
col2 VARCHAR2(1),
col3 VARCHAR2(1));
INSERT INTO SSHARMA.TEST VALUES (NULL, ‘B’, ‘C’);
INSERT INTO SSHARMA.TEST VALUES (’A', NULL, ‘C’);
INSERT INTO SSHARMA.TEST VALUES (NULL, NULL, ‘C’);
INSERT INTO SSHARMA.TEST VALUES (’A', ‘B’, ‘C’);
SELECT COALESCE(col1, col2, col3) FROM TEST;
This will return value as:
B
A
C
A
DUMP
This function returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of the table. The returned result is always in the database character set.
E.g.
SELECT table_name, DUMP(table_name) DMP FROM USER_TABLES;
SELECT table_name, DUMP(table_name, 16) DMP FROM USER_TABLES;
SELECT table_name, DUMP(table_name, 16, 7, 4) DMP FROM USER_TABLES;
All the three statements will return different values.
INSTR (Instring Function)
This function is used to return a position of the string within the string.
This function becomes very useful when combined with Substring function in finding out the position of a specific character.
Syntax:
instr( string1, string2 [, start_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 back start_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.
E.g.
SELECT INSTR(’Oracle on the move’, ‘O’)FROM dual
—would RETURN 1; THE FIRST occurrence OF ‘O’
SELECT INSTR(’Oracle on the move’, ‘o’, 1, 1)FROM dual
—would RETURN 8; THE FIRST occurrence OF ‘o’
SELECT INSTR(’Oracle on the move’, ‘o’, 1, 2)FROM dual
—would RETURN 16; THE SECOND occurrence OF ‘o’
SELECT INSTR(’Oracle on the move’, ‘o’, 1, 3)FROM dual
—would RETURN 0; No third occurrence OF ‘o’
SUBSTR (Substring function)
This function allows extracting a specific substring from the specific string.
Syntax:
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.
E.g.
SELECT SUBSTR(’Oracle on the move’, 8, 2)FROM dual
— would RETURN ‘on’
SELECT SUBSTR(’Oracle on the move’, 8)FROM dual
— would RETURN ‘on the move’
SELECT SUBSTR(’Oracle’, 1, 4)FROM dual
— would RETURN ‘Orac’
SELECT SUBSTR(’Oracle’, -3, 3)FROM dual
— would RETURN ‘cle’
SELECT SUBSTR(’Oracle’, -6, 3)FROM dual
— would RETURN ‘Ora’
SELECT SUBSTR(’LoveOracle’, -8, 2)FROM dual —would RETURN ‘ve’
LENGTH
The LENGTH functions returns the length of char. LENGTH calculates length using characters as defined by the input character set.
E.g. select LENGTH (’Oracle’) from dual;
—returns output as 6
LTRIM
LTRIM removed characters from the left of a string if they are equal to the specified string. If the last parameter is not specified, spaces are removed from the left side.
E.g. SELECT ltrim(’AAAAACLE’,'A’) FROM dual;
—returns output as CLE
RTRIM
RTRIM removed characters from the right of a string if they are equal to the specified string. If the last parameter is not specified, spaces are removed from the right side.
E.g. SELECT RTRIM(’ORACLEFFF’,'F’) FROM dual;
—returns output as ORACLE
LPAD
Add characters to the left of a string until a fixed number is reached.
If the last parameter is not specified, spaces are added to the left.
E.g. SELECT LPAD (’abc’,8,’x') FROM dual;
—returns output as xxxxxabc
RPAD
Add characters to the right of a string until a fixed number is reached If the last parameter is not specified, spaces are added to the right.
E.g. SELECT RPAD (’abc’,8,’x') FROM dual;
—returns output as abcxxxxx
REPLACE
the replace function replaces a sequence of characters in a string with another set of characters.
Syntax: replace( string1, string_to_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.
E.g. SELECT REPLACE(’123123oracle’, ‘123′)FROM dual
–– would RETURN ‘oracle’
SELECT REPLACE(’oracle’, ‘2′, ‘3′)FROM dual
— would RETURN ‘333oracle’
SELECT REPLACE(’0000123′, ‘0′)FROM dual
— would RETURN ‘123′
SELECT REPLACE(’0000123′, ‘0′, ‘ ‘)FROM dual
— would RETURN ‘ 123′
TRANSLATE
The 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..
Syntax: translate( string1, string_to_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 - All characters in the string_to_replace will be replaced with the corresponding character in the replacement_string.
E.g. SELECT TRANSLATE(’1tech23′, ‘123′, ‘456′) FROM dual
— would return ‘4tech56′
SELECT TRANSLATE(’222tech’, ‘2ec’, ‘3it’) FROM dual
— would return ‘333tith’
Besides these are other function like date functions which are used for date conversion format. The above listed functions are the major functions used in oracle database for performing day to day operations.
Related Posts
- Oracle PL/SQL: Advanced Cursors
- Oracle PL/SQL: Cursors
- Oracle PL/SQL: Loops and Conditional Statements
- Oracle PL/SQL Datatypes
- Oracle PL/SQL: Is Null / Is Not Null
Tags: Character Functions in oracle, Oracle Basic Functions, Oracle Functions for Developers, Oracle Inbuilt Functions
