A character function is a function that takes one or more character values as parameters and returns either a character value or a number value.
The Built-In Character Functions
- ASCII
- CHR
- CONCAT
- INITCAP
- LOWER
- LPAD
- LTRIM
- NLS_INITCAP
- REGEXP_REPLACE
- REGEXP_SUBSTR
- REPLACE
- RPAD
- RTRIM
- SOUNDEX
- SUBSTR
- TRANSLATE
- TREAT
- TRIM
- UPPER
ASCII Function
Returns the ASCII code of a character.
Examples
SELECT ASCII('A') FROM DUAL;
ASCII('A')
----------
65
SELECT ASCII('a') FROM DUAL;
ASCII('A')
----------
97
SELECT ASCII('Z') FROM DUAL;
ASCII('Z')
----------
90
CHR Function
Returns the character associated with the specified collating code.
The CHR function is the inverse of ASCII. It returns a VARCHAR2 character (length 1) that corresponds to the location in the collating sequence provided as a parameter.
Examples
SELECT CHR(65) FROM DUAL;
CHR(65)
-------
A
SELECT CHR(97) FROM DUAL;
CHR(97)
-------
a
SELECT CHR(67)||CHR(65)||CHR(84) "Cat" FROM DUAL;
Cat
---
CAT
CONCAT Function
Concatenates two strings into one.
The CONCAT function concatenates by taking two VARCHAR2 strings and returning those strings appended together in the order specified.
The CONCAT function is equivalent to the concatenation operator (||).
Examples
select CONCAT('abc', 'defg') from dual
CONCAT('abc', 'defg')
----------------------
abcdefg
SELECT CONCAT(FIRST_NAME, LAST_NAME) FULL_NAME, DEPARTMENT_ID
FROM EMPLOYEES;
FULL_NAME DEPARTMENT_ID
------------------- -----------------
DonaldOConnell 50
DouglasGrant 50
JenniferWhalen 10
MichaelHartstein 20
PatFay 20
INITCAP Function
INITCAP function sets the first character in each word to uppercase and the rest to lowercase.
The return value is the same datatype as char.
Examples
SELECT INITCAP('the soap') "Initcap" FROM DUAL;
Initcap
---------
The Soap
LOWER Function
LOWER function 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.
Examples
SELECT LOWER('The Soap') "lower" FROM DUAL;
lower
---------
the soap
SELECT LOWER('THE SOAP') "lower" FROM DUAL;
lower
---------
the soap
LPAD Function
LPAD function pads the left-side of a string with a specific set of characters (when string1 is not null). This function is useful for formatting the output of a query.
Syntax
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.
Examples
SELECT LPAD('Oracle',10,'*') "LPAD example" FROM DUAL;
LPAD example
---------------
****Oracle
SELECT LPAD('12345',10,'0') "LPAD example" FROM DUAL;
LPAD example
---------------
0000012345
LTRIM Function
In Oracle/PLSQL, the LTRIM Function removes all specified characters from the left-hand side of a string.
Syntax
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.
Examples
select ltrim('123123Tech', '123') from dual
ltrim('123123Tech', '123')
--------------------------
Tech
SELECT product_name, LTRIM(product_name, 'Monitor ') "Short Name"
FROM products
WHERE product_name LIKE 'Monitor%';
PRODUCT_NAME Short Name
-------------------- ---------------
Monitor 17/HR 17/HR
Monitor 17/HR/F 17/HR/F
Monitor 17/SD 17/SD
Monitor 19/SD 19/SD
Monitor 19/SD/M 19/SD/M
ltrim('000123', '0'); would return '123'
ltrim('123123Tech', '123'); would return 'Tech'
ltrim('123123Tech123', '123'); would return 'Tech123'
NLS_INITCAP Function
NLS_INITCAP Function returns string with the first letter of each word in uppercase using the special linguistic requirements of case conversions.
The following examples show how the linguistic sort sequence results in a different return value from the function.
Examples
SELECT NLS_INITCAP('ijsland') "InitCap" FROM DUAL;
InitCap
-------
Ijsland
SELECT NLS_INITCAP('ijsland', 'NLS_SORT = XDutch') "InitCap"
FROM DUAL;
InitCap
-------
IJsland
REPLACE Function
REPLACE Function replaces a sequence of characters in a string with another set of characters.
REPLACE provides functionality related to that provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings.
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.
Examples
SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL;
Changes
--------------
BLACK and BLUE
replace('123123tech', '123'); would return 'tech'
replace('123tech123', '123'); would return 'tech'
replace('222tech', '2', '3'); would return '333tech'
replace('0000123', '0'); would return '123'
replace('0000123', '0', ' '); would return ' 123'
RPAD Function
RPAD Function pads the right-side of a string with a specific set of characters. This function is useful for formatting the output of a query.
Syntax
rpad( string1, padded_length, [ pad_string ] )
string1 is the string to pad characters to (the right-hand side), when string1 is not null.
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.
Examples
rpad('tech', 7); would return 'tech '
rpad('tech', 2); would return 'te'
rpad('tech', 8, '0'); would return 'tech0000'
RTRIM Function
RTRIM Function removes all specified characters from the right-hand side of a string.
Syntax
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.
Examples
SELECT RTRIM('BROWNING: ./=./=./=./=./=.=','/=.') "RTRIM example"
FROM DUAL;
RTRIM exam
----------
BROWNING:
SOUNDEX Function
SOUNDEX Function lets you compare words that are spelled differently, but sound alike in English.
This function returns a phonetic representation (the way it sounds) of a string.
SOUNDEX Algorithm
- The soundex return value will always begin with the first letter of string1 and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.
- Assign numbers to the remaining letters (after the first) as follows:
b, f, p, v = 1
c, g, j, k, q, s, x, z = 2
d, t = 3
l = 4
m, n = 5
r = 6
- The soundex function is not case-sensitive. What this means is that both uppercase and lowercase characters will generate the same soundex return value.
- Return the first four bytes padded with 0.
Examples
SELECT last_name, first_name
FROM hr.employees
WHERE SOUNDEX(last_name) = SOUNDEX('SMYTHE');
LAST_NAME FIRST_NAME
---------- ----------
Smith Lindsey
Smith William
soundex('tech on the net'); would return 'T253'
soundex('TECH ON THE NET'); would return 'T253'
soundex('apples'); would return 'A142'
soundex('apples are great'); would return 'A142'
soundex('applus'); would return 'A142'
SUBSTR Function
SUBSTR Function allows you to extract a substring from a string.
Syntax
substr( string, start_position, [ substring_length ] )
string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
substring_length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.
- 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 substring_length is a negative number, then substr will return a NULL value.
Examples
SELECT SUBSTR('ABCDEFG',3,4) "Substring"
FROM DUAL;
Substring
---------
CDEF
SELECT SUBSTR('ABCDEFG',-5,4) "Substring"
FROM DUAL;
Substring
---------
CDEF
TRANSLATE Function
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.
Examples
SELECT TRANSLATE('SQL*Plus User''s Guide', ' */''', '___') FROM DUAL;
TRANSLATE('SQL*PLUSU
--------------------
SQL_Plus_Users_Guide
TREAT Function
The Oracle TREAT function allows you to change the declared type of the expression used in TREAT. This function comes in handy when you have a subtype that is more specific to your data and you want to convert the parent type to the more specific one.
Examples
CREATE TYPE PERSON AS OBJECT (
FIRST_NAME VARCHAR2(15),
LAST_NAME VARCHAR2(15),
SALARY NUMBER(10));
CREATE TABLE PERSON_TABLE
(
ID NUMBER(4) PRIMARY KEY,
PERSON_INFO PERSON,
CREATED_DATE DATE
);
INSERT INTO PERSON_TABLE
VALUES(1,PERSON('SMITH','BLAKE',800),SYSDATE);
INSERT INTO PERSON_TABLE
VALUES(2,PERSON('ALLEN','WARD',800),SYSDATE);
SELECT * FROM PERSON_TABLE
ID PERSON_INFO CREATED_DATE
--- -------------------- --------------
1 (SMITH; BLAKE; 800) 06/05/2013
2 (ALLEN; WARD; 900) 06/05/2013
SELECT ID,TREAT(PERSON_INFO AS PERSON).FIRST_NAME FIRST_NAME,
TREAT(PERSON_INFO AS PERSON).LAST_NAME LAST_NAME,
TREAT(PERSON_INFO AS PERSON).SALARY SALARY,
CREATED_DATE
FROM PERSON_TABLE
ID FIRST_NAME LAST_NAME SALARY CREATED_DATE
----- ------------ ------------ ------- ------------
1 SMITH BLAKE 800 06/05/2013
2 ALLEN WARD 900 06/05/2013
TRIM Function
TRIM Function enables you to remove leading or trailing characters (or both) from a character string.
Syntax
trim( [ leading | trailing | both [ trim_character ] string1 )
- LEADING- removes any leading characters equal to trim_character.
- TRAILING- removes any trailing characters equal to trim_character.
- BOTH- removes leading and trailing characters equal to trim_character.
- If trim_character parameter is omitted, TRIM Function will remove all leading and trailing spaces from string1. string1 is the string to trim.
- If you do not specify a value for the first parameter (leading, trailing, both), TRIM Function will remove trim_character from both the front and end of string1.
Examples
trim(' tech ') would return 'tech'
trim(' ' from ' tech ') would return 'tech'
trim(leading '0' from '000123') would return '123'
trim(trailing '1' from 'Tech1') would return 'Tech'
trim(both '1' from '123Tech111') would return '23Tech'
UPPER Function
UPPER Function 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.
Examples
SELECT UPPER(last_name) "Uppercase"
FROM employees;
upper('Tech on the Net'); would return 'TECH ON THE NET'
upper('george burns 123 '); would return 'GEORGE BURNS 123 '
