Oracle String Functions

Yusuf SEZER

5 years ago

This article contains information about oracle string functions such as length, substr, concat, trim in Oracle database management system.

What is Oracle string function?

Oracle has several functions for manipulating character or text data.

ASCII function in Oracle(pl sql char to ascii)

ASCII Function returns the numerical equivalent of the character value in the ASCII table.

SELECT ASCII('Y') AS Y FROM DUAL;

1	SELECT ASCII('Y') AS Y FROM DUAL;

NOTE: Dual table is a virtual table used in various operations.

CHR Function in Oracle(pl sql ascii to char)

CHR Function returns the character equivalent of the numerical value in the ASCII table.

SELECT CHR(89) AS "Y" FROM DUAL;

1	SELECT CHR(89) AS "Y" FROM DUAL;

CONCAT Function in Oracle

The CONCAT function is used for string concatenation.

SELECT CONCAT('Yusuf', ' Sefa') AS AD FROM DUAL;

1	SELECT CONCAT('Yusuf', ' Sefa') AS AD FROM DUAL;

The function can be nested for more than one value.

SELECT CONCAT(CONCAT('Yusuf', ' Sefa'), ' SEZER') AS FullName FROM DUAL;

1	SELECT CONCAT(CONCAT('Yusuf', ' Sefa'), ' SEZER') AS FullName FROM DUAL;

|| operator can also be used for string concatenation operation.

SELECT 'Yusuf' || ' Sefa' AS Name FROM DUAL;

1	SELECT 'Yusuf' \|\| ' Sefa' AS Name FROM DUAL;

SELECT 'Yusuf' || ' Sefa' || ' SEZER' AS FullName FROM DUAL;

1	SELECT 'Yusuf' \|\| ' Sefa' \|\| ' SEZER' AS FullName FROM DUAL;

INITCAP Function in Oracle

This function set the first letter of each value in a String expression as uppercase and the others as lowercase letters.

SELECT INITCAP('YUSUF SEFA') AS Name FROM DUAL;

1	SELECT INITCAP('YUSUF SEFA') AS Name FROM DUAL;

LENGHT Function in Oracle

This function returns the length of a string.

SELECT LENGTH('YUSUF SEFA SEZER') AS CHARACTER_LENGHT FROM DUAL;

1	SELECT LENGTH('YUSUF SEFA SEZER') AS CHARACTER_LENGHT FROM DUAL;

There is also LENGHT2, LENGHT4, LENGHTB and LENGHTC functions in oracle. sytanx and logic is the same.

LENGHT2 use UCS2 code points,

LENGHT4 use UCS4 code points,

LENGHTB use bytes instead of characters,

LENGHTC use Unicode complete characters.

INSTR Function in Oracle

Returns the location of the searched value in a string expression.

SELECT INSTR('YUSUF SEFA SEZER', 'U') AS Location_of_U FROM DUAL;

1	SELECT INSTR('YUSUF SEFA SEZER', 'U') AS Location_of_U FROM DUAL;

The parameters of the function are as follows.

INSTR(string_to_search,substring,position, occurence)

string -> string to search

substring -> text to search in string

position -> this parameter is optinal. indicates where the search begins.

occurence -> this parameter is optional. The default value is 1. It means that you will search for first substring. If you want to search second substring in the string you must write 2 for this parameter.

There is also INSTR2, INSTR4, INSTRB and INSTRC functions in oracle. sytanx and logic is the same.

INSTR2 use UCS2 code points,

INSTR4 use UCS4 code points,

INSTRB use bytes instead of characters,

INSTRC use Unicode complete characters.

Examples:

SELECT INSTR('dbtut', 't') FROM DUAL;
Result: 3   (the first occurrence of 't')

SELECT INSTR('dbtut', 't', 1, 1) FROM DUAL;
Result: 2   (the first occurrence of 't')

SELECT INSTR('dbtut', 't', 1, 2) FROM DUAL;
Result: 5  (the second occurrence of 't')

SELECT INSTR('dbtut', 't') FROM DUAL;

Result: 3 (the first occurrence of 't')

SELECT INSTR('dbtut', 't', 1, 1) FROM DUAL;

Result: 2 (the first occurrence of 't')

SELECT INSTR('dbtut', 't', 1, 2) FROM DUAL;

Result: 5 (the second occurrence of 't')

SUBSTR Function in Oracle

This function is used to extract a specific part from a String expression.

The parameters of the function are as follows.

SUBSTR(string, start_position, lenght)

string -> string to search

start_position -> start position for extracting

position -> this parameter is optinal. indicates extract lenght.

Examples:

SELECT SUBSTR('dbtut.com', 2, 2) FROM DUAL;
Result: 'bt'

SELECT SUBSTR('dbtut.com', 2) FROM DUAL;
Result: 'btut.com'

SELECT SUBSTR('dbtut.com', 2, 2) FROM DUAL;

Result: 'bt'

SELECT SUBSTR('dbtut.com', 2) FROM DUAL;

Result: 'btut.com'

TRIM Function in Oracle

This function removes spaces at the beginning and end of the string expression by default.

SELECT TRIM('   YUSUF SEFA SEZER   ') AS TRIM, LENGTH(TRIM('   YUSUF SEFA SEZER   ')) AS Name_Lenght FROM DUAL;

1	SELECT TRIM(' YUSUF SEFA SEZER ') AS TRIM, LENGTH(TRIM(' YUSUF SEFA SEZER ')) AS Name_Lenght FROM DUAL;

The function has several uses. You can find examples below.

Examples:

SELECT TRIM('   dbtut   ') FROM DUAL;
Result: 'dbtut'

SELECT TRIM(' '  FROM  '   dbtut   ') FROM DUAL;
Result: 'dbtut'

SELECT TRIM(LEADING '0' FROM '000dbtut0') FROM DUAL;
Result: 'dbtut0'

SELECT TRIM(TRAILING '1' FROM '1dbtut1') FROM DUAL;
Result: '1dbtut'

SELECT TRIM(BOTH '1' FROM '11dbtut11') FROM DUAL;
Result: 'dbtut'

SELECT TRIM(' dbtut ') FROM DUAL;

Result: 'dbtut'

SELECT TRIM(' ' FROM ' dbtut ') FROM DUAL;

Result: 'dbtut'

SELECT TRIM(LEADING '0' FROM '000dbtut0') FROM DUAL;

Result: 'dbtut0'

SELECT TRIM(TRAILING '1' FROM '1dbtut1') FROM DUAL;

Result: '1dbtut'

SELECT TRIM(BOTH '1' FROM '11dbtut11') FROM DUAL;

Result: 'dbtut'

RTRIM Function in Oracle

This function removes spaces at the end of the string expression by default. But if you set a second parameter, it will remove the character(s) you set right end of the string.

Examples:

SELECT RTRIM('dbtut   ') FROM DUAL;
Result: 'dbtut'

SELECT RTRIM('dbtut   ', ' ') FROM DUAL;
Result: 'dbtut'

SELECT RTRIM('dbtut.com', '.com') FROM DUAL;
Result: 'dbtut'

SELECT RTRIM('dbtut.com13254', '12345') FROM DUAL;
Result: 'dbtut.com'

SELECT RTRIM('dbtut ') FROM DUAL;

Result: 'dbtut'

SELECT RTRIM('dbtut ', ' ') FROM DUAL;

Result: 'dbtut'

SELECT RTRIM('dbtut.com', '.com') FROM DUAL;

Result: 'dbtut'

SELECT RTRIM('dbtut.com13254', '12345') FROM DUAL;

Result: 'dbtut.com'

LTRIM Function in Oracle

This function removes spaces from left side of the string expression by default. But if you set a second parameter, it will remove the character(s) you set from left side of the string.

Examples:

SELECT LTRIM('   dbtut') FROM DUAL;
Result: 'dbtut'

SELECT LTRIM('   dbtut', ' ') FROM DUAL;
Result: 'dbtut'

SELECT LTRIM('https://dbtut.com', 'https://') FROM DUAL;
Result: 'dbtut.com'

SELECT LTRIM('13254dbtut.com', '12345') FROM DUAL;
Result: 'dbtut.com'

SELECT LTRIM(' dbtut') FROM DUAL;

Result: 'dbtut'

SELECT LTRIM(' dbtut', ' ') FROM DUAL;

Result: 'dbtut'

SELECT LTRIM('https://dbtut.com', 'https://') FROM DUAL;

Result: 'dbtut.com'

SELECT LTRIM('13254dbtut.com', '12345') FROM DUAL;

Result: 'dbtut.com'

RPAD Function in Oracle

This function completes the end of the string expression according to the value specified by the parameter.

The parameters of the function are as follows.

RPAD(string, lenght, character_to_complete)

string -> string to search

lenght -> total lenght of string after function executed

character_to_complete -> this parameter is optinal. If you set this parameter, it adds this character to the string as many as the number specified in the second parameter.

Examples:

SELECT RPAD('dbtut', 8) FROM DUAL;
Result: 'dbtut   '

SELECT RPAD('dbtut', 4) FROM DUAL;
Result: 'dbtu'

SELECT RPAD('dbtut', 6, '0') FROM DUAL;
Result: 'dbtut0'

SELECT RPAD('dbtut', 5, 'x') FROM DUAL;
Result: 'dbtut'

SELECT RPAD('dbtut', 8, 'x') FROM DUAL;
Result: 'dbtutxxx'

SELECT RPAD('dbtut', 8) FROM DUAL;

Result: 'dbtut '

SELECT RPAD('dbtut', 4) FROM DUAL;

Result: 'dbtu'

SELECT RPAD('dbtut', 6, '0') FROM DUAL;

Result: 'dbtut0'

SELECT RPAD('dbtut', 5, 'x') FROM DUAL;

Result: 'dbtut'

SELECT RPAD('dbtut', 8, 'x') FROM DUAL;

Result: 'dbtutxxx'

LPAD Function in Oracle

This function completes the start of the string expression according to the value specified by the parameter.

The parameters of the function are as follows.

LPAD(string, lenght, character_to_complete)

string -> string to search

lenght -> total lenght of string after function executed

character_to_complete -> this parameter is optinal. If you set this parameter, it adds this character to the start of the string as many as the number specified in the second parameter.

Examples:

SELECT LPAD('dbtut', 8) FROM DUAL;
Result: '   dbtut'

SELECT LPAD('dbtut', 4) FROM DUAL;
Result: 'dbtu'

SELECT LPAD('dbtut', 6, '0') FROM DUAL;
Result: '0dbtut'

SELECT LPAD('dbtut', 5, 'x') FROM DUAL;
Result: 'dbtut'

SELECT LPAD('dbtut', 8, 'x') FROM DUAL;
Result: 'xxxdbtut'

SELECT LPAD('dbtut', 8) FROM DUAL;

Result: ' dbtut'

SELECT LPAD('dbtut', 4) FROM DUAL;

Result: 'dbtu'

SELECT LPAD('dbtut', 6, '0') FROM DUAL;

Result: '0dbtut'

SELECT LPAD('dbtut', 5, 'x') FROM DUAL;

Result: 'dbtut'

SELECT LPAD('dbtut', 8, 'x') FROM DUAL;

Result: 'xxxdbtut'

COMPOSE function in Oracle

This function returns unicode value of any data type. The data type must be able to converted to a string. Because function takes its parameter as string.

The parameters of the function are as follows.

COMPOSE(string)

string -> string to converted unicode

SELECT COMPOSE('o' || unistr('\0308') ) FROM DUAL;
Result: ö

SELECT COMPOSE('a' || unistr('\0302') ) FROM DUAL;
Result: â

SELECT COMPOSE('o' || unistr('\0308') ) FROM DUAL;

Result: ö

SELECT COMPOSE('a' || unistr('\0302') ) FROM DUAL;

Result: â

Decompose Function in Oracle

Decompose function decompose a string and returns unicode string.

Sample Usage:

SELECT DECOMPOSE ('â') FROM DUAL; 

DECOMPOSE
---------
a^

SELECT DECOMPOSE ('â') FROM DUAL;

DECOMPOSE

---------

Convert Function in Oracle

This function convert one character set to another character set. You can query V$NLS_VALID_VALUES to get character sets.

SELECT * FROM V$NLS_VALID_VALUES WHERE parameter = 'CHARACTERSET'

1	SELECT * FROM V$NLS_VALID_VALUES WHERE parameter = 'CHARACTERSET'

Sample Usage:

SELECT CONVERT('Ä', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL;

1	SELECT CONVERT('Ä', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL;

Replace Function in Oracle

This function replaces characters in a string with another characters.

The parameters of the function are as follows.

REPLACE(string, string_to_be_replaced,replace_string)

string -> main string

string_to_be_replaced -> string to be replaced

replace_string -> this parameter is optinal. If you set this parameter, it replaces second parameter with the string specified in this parameter. If you not set this parameter, this function removes the character specified in the second parameter from the string.

Examples:

SELECT REPLACE('xxxdbtut', 'xxx') FROM DUAL;
Result: 'dbtut'

SELECT REPLACE('xxxdbtutxxx', 'xxx') FROM DUAL;
Result:'dbtut'

SELECT REPLACE('dbtut.xxx', 'xxx', 'com') FROM DUAL;
Result: 'dbtut.com'

SELECT REPLACE('xxxdbtut', 'xxx') FROM DUAL;

Result: 'dbtut'

SELECT REPLACE('xxxdbtutxxx', 'xxx') FROM DUAL;

Result:'dbtut'

SELECT REPLACE('dbtut.xxx', 'xxx', 'com') FROM DUAL;

Result: 'dbtut.com'

UPPER Function in Oracle

This functions converts all lowercases to uppercases in a string.

Sample Usage:

SELECT UPPER('dbtut111') FROM DUAL;
Result: 'DBTUT111'

1 2	SELECT UPPER('dbtut111') FROM DUAL; Result: 'DBTUT111'

LOWER Function in Oracle

This functions converts all uppercases to lowercases in a string.

Sample Usage:

SELECT LOWER('DBTUT111') FROM DUAL;
Result: 'dbtut111'

1 2	SELECT LOWER('DBTUT111') FROM DUAL; Result: 'dbtut111'

VSIZE Function in Oracle

This function returns number of bytes of expression.

Sample Usages:

SELECT VSIZE('dbtut') FROM DUAL;
Result: 5

SELECT VSIZE(null) FROM DUAL;
Result: <null>

SELECT surname, VSIZE (surname) "BYTES"      
  FROM staffs
  WHERE staff_id = 1;

Result: 
LAST_NAME            BYTES
--------------- ----------
CAKIR                   5

SELECT VSIZE('dbtut') FROM DUAL;

Result: 5

SELECT VSIZE(null) FROM DUAL;

Result: <null>

SELECT surname, VSIZE (surname) "BYTES"

FROM staffs

WHERE staff_id = 1;

Result:

LAST_NAME BYTES

--------------- ----------

CAKIR 5

Difference between length and vsize function in oracle

Lenght function returns lenght of a string, but VSIZE function returns number of bytes. So, a character may be 2 byte.