Oracle Character Functions

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

1. 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.
2. 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

3. The soundex function is not case-sensitive. What this means is that both uppercase and lowercase characters will generate the same soundex return value.
4. 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   '