Oracle String Functions

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.

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.

CONCAT Function in Oracle

The CONCAT function is used for string concatenation.

The function can be nested for more than one value.

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

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.

LENGHT Function in Oracle

This function returns the length of a string.

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.

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:

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:

TRIM Function in Oracle

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

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

Examples:

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:

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:

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:

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:

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

Decompose Function in Oracle

Decompose function decompose a string and returns unicode string.

Sample Usage:

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.

Sample Usage:

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:

UPPER Function in Oracle

This functions converts all lowercases to uppercases in a string.

Sample Usage:

LOWER Function in Oracle

This functions converts all uppercases to lowercases in a string.

Sample Usage:

VSIZE Function in Oracle

This function returns number of bytes of expression.

Sample Usages:

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.