String Functions in Oracle with Examples

Back to: Oracle Tutorials for Beginners and Professionals

String Functions in Oracle with Examples

In this article, I am going to discuss String Functions in Oracle with Examples. Please read our previous article where we discussed Numeric Functions in Oracle. At the end of this article, you will understand the following oracle String Functions with examples.

LENGTH() Function in Oracle
LOWER() Function in Oracle
UPPER() Function in Oracle
INITCAP() Function in Oracle
LTIRM() Function in Oracle
RTRIM() Function in Oracle
TRIM() Function in Oracle
LPAD() Function in Oracle
RPAD() Function in Oracle
CONCAT() Function in Oracle
REPLACE() Function in Oracle
TRANSLATE() Function in Oracle
SUBSTR() Function in Oracle
INSTR() Function in Oracle

Note: The Built-In string functions in oracle are used with expressions to calculate values and manipulate the data. These built-in string functions can be used anywhere, where expressions are allowed.

LENGTH() Function in Oracle:

The String LENGTH function in Oracle is used to return the length of a given string. Here, the given string can be any of the data types such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is always going to be of datatype NUMBER. If the given string has data type CHAR, then the LENGTH function includes all the trailing blanks. If the given string is null, then the LENGTH function returns null. LENGTHB uses bytes instead of characters. LENGTHC uses Unicode complete characters. LENGTH2 uses UCS2 code points. LENGTH4 uses UCS4 code points. The following is the syntax to use the LENGTH function in oracle.

Note: The LENGTHB function is supported for single-byte LOBs only. It cannot be used with CLOB and NCLOB data in a multibyte character set.

Example: The following example uses the LENGTH function using a single-byte database character set

SELECT LENGTH('HELLO') FROM DUAL;

Output:

Example: The following example uses the LENGTH function using a single-byte database character set

SELECT LENGTH('GOOD MORNING') FROM DUAL;

Output:

Example: The following example assumes a double-byte database character set

SELECT LENGTHB('HELLO') FROM DUAL;

Output:

Example: The following example assumes a double-byte database character set

SELECT LENGTHB('GOOD MORNING') FROM DUAL;

Output:

LOWER() Function in Oracle:

The string LOWER() function in Oracle is used to return a specified character expression in lowercase letters. The following is the syntax to use the LOWER function in oracle.

Syntax: LOWER(CharExpression)

Parameter:

CharExpression: The given character expression. The Data type can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB

Example:

SELECT LOWER('HELLO') FROM DUAL;

Output:

UPPER() Function in Oracle:

The string UPPER() function in Oracle is used to return a specified character expression in uppercase letters. The following is the syntax to use the UPPER function in oracle.

Syntax: UPPER(CharExpression)

Parameter:

CharExpression: The given character expression. The Data type can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB

Example:

SELECT LOWER('hello') FROM DUAL;

Output:

INITCAP() Function in Oracle:

The string INITCAP() function in Oracle is used to set the first letter of each word in uppercase, and rest all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric. The given string can be of any of the data types such as CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The return value is the same datatype as the given string. The database sets the case of the initial characters based on the binary mapping defined for the underlying character set. The following is the syntax to use the INITCAP function in oracle.

Syntax: INITCAP(STRING)

Example:

SELECT INITCAP('hello') FROM DUAL;

Output:

Example:

SELECT INITCAP('welcome TO OrCale tuTOrials') INICAP FROM DUAL;

Output:

Note: This function does not support CLOB data directly. However, CLOB can be passed in as arguments through implicit data type conversion.

LTIRM() Function in Oracle:

The string LTRIM() Function in Oracle is used to remove the unwanted spaces (or) unwanted characters from the left side of the given string. The string returned is of VARCHAR2 datatype if char is a character datatype and a LOB if char is a LOB datatype. The following is the syntax to use the LTRIM function in oracle.

Syntax: LTRIM(STRING1 [,STRING2])

Parameters:

STRING1: The string to trim the characters from the left-hand side. It can be of any char data type such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
STRING2: This is optional and this parameter specifies the String which is used for trimming. It can be of any char data type such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Example:

SELECT LTRIM(' ANURAG') TRIML FROM DUAL;

Output:

Example:

SELECT LTRIM('XXXXXXANURAG','X') TRIML FROM DUAL;

Output:

Example:

SELECT LTRIM('123ANURAG','123') TRIML FROM DUAL;

Output:

Example:

SELECT LTRIM('123ANURAG',' ANURAG') TRIML FROM DUAL;

Output:

RTRIM() Function in Oracle:

The string RTRIM() Function in Oracle is used to remove the unwanted spaces (or) unwanted characters from the right-hand side of the given string. The string returned is of VARCHAR2 datatype if char is a character datatype and LOB if char is a LOB datatype. The following is the syntax to use the LTRIM function in oracle.

Syntax: RTRIM(STRING1 [,STRING2])

Parameters:

STRING1: The string to trim the characters from the right-hand side. It can be of any char data type such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
STRING2: This is optional and this parameter specifies the String which is used for trimming. It can be of any char data type such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Example:

SELECT RTRIM(' ANURAG ') TRIMR FROM DUAL;

Output:

Example:

SELECT RTRIM('ANURAG ') TRIMR FROM DUAL;

Output:

Example:

SELECT RTRIM('ANURAGXXXXXX','X') TRIMR FROM DUAL;

Output:

Example:

SELECT RTRIM('ANURAG123','123') TRIMR FROM DUAL;

Output:

Example:

SELECT RTRIM('ANURAG123',' ANURAG') TRIMR FROM DUAL;

Output:

TRIM() Function in Oracle:

The string TRIM function in Oracle is used to remove the leading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, then you must enclose it in single quotes. The following is the syntax to use the TRIM function in oracle.

Points to Note:

If you specify LEADING, then Oracle Database removes any leading characters equal to trim_character.
If you specify TRAILING, then Oracle removes any trailing characters equal to trim_character.
If you specify BOTH or none of the three, then Oracle removes leading and trailing characters equal to trim_character.
If you do not specify trim_character, then the default value is a blank space.
If you specify only trim_source, then Oracle removes leading and trailing blank spaces.
The function returns a value with datatype VARCHAR2. The maximum length of the value is the length of trim_source.
If either trim_source or trim_character is null, then the TRIM function returns null.

Both trim_character and trim_source can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype if trim_source is a character datatype and a LOB if trim_source is a LOB datatype. The return string is in the same character set as trim_source.

Example:

SELECT TRIM(' Removing Leading and Trailing White Spaces ') LRTRIM FROM DUAL;

Output:

Example:

SELECT TRIM(' Removing Leading White Spaces') LRTRIM FROM DUAL;

Output:

Example:

SELECT TRIM('Removing Trailing White Spaces ') LRTRIM FROM DUAL;

Output:

Example:

SELECT TRIM(LEADING '6' FROM '660123') LRTRIM FROM DUAL;

Output:

Example:

SELECT TRIM(TRAILING '5' FROM '123455') LRTRIM FROM DUAL;

Output:

LPAD() Function in Oracle:

The string LPAD() function in Oracle is used to pad the left side of a string with a specific set of characters. So, in simple words, we can say that this LPAD function is used to fill a string with a specific character on the left side of a given string. This LPAD function is very useful for formatting the output of a query. The following is the syntax to use the LPAD function in oracle.

Syntax: LPAD(expr1, n [, expr2 ])

Parameters:

expr1: Original string. It can be of any data type such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
n: It is an integer number or a value that can be implicitly converted to a NUMBER integer indicating the total length of the string (in characters) returned after padding.
expr2: It is the String that is used for left padding. It can also be of any data type such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Note: The string returned is of VARCHAR2 datatype if expr1 is a character datatype and a LOB if expr1 is a LOB datatype. The string returned is in the same character set as expr1. If you do not specify expr2, then the default is a single blank. If expr1 is longer than n, then this function returns the portion of expr1 that fits in n.

String LPAD() Function Examples in Oracle

In the below example, the first argument specifies a string of 5 characters, the second argument specifies that the length of the string returned after padding will be 10 characters and the third argument specifies the string to be used for left padding. So, 5 characters (10-5) being used for left padding and the function thus returns ‘+++++Hello’.

SELECT LPAD('Hello',10,'+') PADL FROM DUAL;

Output:

String LPAD() function Example with less than the original string in Oracle

The following example returns ‘Hell’. This happens because the first argument has 5 characters, the second argument 4 is the total number of characters to be returned after left padding and the third argument is the padding string i.e. @. Since the total number of characters after padding is less than the total number of characters present in the first argument, so to meet the condition, one character is omitted from the actual string (i.e. from the first argument).

SELECT LPAD('Hello',4,'@') PADL FROM DUAL;

Output:

RPAD() Function in Oracle:

The string RPAD() function in Oracle is used to pad the right side of a given string with a specific set of characters. So, in simple words, we can say that the RPAD function is used to fill a string with a specific character on the right side of a given string. This RPAD function is useful for formatting the output of a query. The following is the syntax to use the RPAD function in oracle.

Syntax: RPAD(expr1, n [, expr2 ])

Parameters:

expr1: Original string. It can be of any data type such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
n: It is an integer number or a value that can be implicitly converted to a NUMBER integer indicating the total length of the string (in characters) returned after padding.
expr2: It is the string that is used for right padding. It can also be of any data type such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

String RPAD() Function Examples in Oracle

In the below example, the first argument specifies a string of 5 characters, the second argument specifies that the length of the string returned after right padding will be 10 characters and the third argument specifies the string to be used for right padding. So, 5 characters (10-5) are used for right padding and the function thus returns ‘Hello@@@@@’.

SELECT RPAD('Hello',10,'@') PADR FROM DUAL;

Output:

Oracle String RPAD() Function Examples with less than the original string

The following example returns ‘Hell’. This happens because the first argument has 5 characters, the second argument 4 is the total number of characters to be returned after right padding and the third argument is the padding string i.e. @. Since the total number of characters after padding is less than the total number of characters present in the first argument, so to meet the condition, one character is omitted from the actual string (i.e. from the first argument).

SELECT RPAD('Hello',4,'@') PADR FROM DUAL;

Output:

CONCAT() Function in Oracle:

The string CONCAT() function in Oracle is used to return the result (a string) of concatenating two string values. So, in simple words, we can say that this function is used to concatenate two expressions. This function is equivalent to the concatenation operator (||). The following is the syntax to use the CONCAT function in oracle.

Syntax: CONCAT(char1, char2)

Parameters:

char1: It is a string value to concatenate to the other values. It can be of any data type such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
char2: It is also a string value to concatenate to the other values. It can also be of any data type such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Return Type:

The string returned is in the same character set as char1. Its data type depends on the data types of the arguments. In concatenations of two different data types, Oracle Database returns the datatype that results in a lossless conversion. Therefore, if one of the arguments is a LOB, then the returned value is a LOB. If one of the arguments is a national datatype, then the returned value is a national datatype. For example:

CONCAT(CLOB, NCLOB) returns NCLOB
CONCAT(NCLOB, NCHAR) returns NCLOB
CONCAT(NCLOB, CHAR) returns NCLOB
CONCAT(NCHAR, CLOB) returns NCLOB

Example:

SELECT CONCAT('Good', 'Morning') ConcatString FROM DUAL;

Output:

REPLACE() Function in Oracle:

The string REPLACE function in Oracle is used to return a string with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed. When search_string is null, then the string is returned. So, basically this string REPLACE function in Oracle is used to replace one string with another string. The following is the syntax to use the REPLACE function in oracle.

Syntax: REPLACE (String, Search_String [, Replacement_String ])

Parameters:

String: The original string and can be of any data type such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
Search_String: This is the string to be searched. It can be of any data type such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
Replacement_String: This is the string to be replaced. It can also be of any data types such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Note: The string returned is in the same character set as char. The function returns VARCHAR2 if the first argument is not a LOB and returns CLOB if the first argument is a LOB. The Oracle String REPLACE function provides similar functionality related to that provided by the TRANSLATE function. The TRANSLATE function provides single-character, one-to-one substitution whereas the REPLACE function lets you substitute one string for another as well as to remove character strings.

Example:

SELECT REPLACE('JACK and JUE','J','BL') "New String" FROM DUAL;

Output:

Example:

SELECT REPLACE('MAN and MAT','M','F') "New String" FROM DUAL;

Output:

TRANSLATE() Function in Oracle:

The string TRANSLATE function in Oracle is used to return a string with all occurrences of each character specified in another string as 2nd argument replaced by its corresponding character specified in the 3rd argument. So, in simple words, we can say that this function is used to translate a single char with another single char. The Oracle string TRANSLATE function does not support the CLOB data type directly. However, the CLOB data can be passed in as arguments through implicit data type conversion. The following is the syntax to use the TRANSLATE function in Oracle.

Syntax: TRANSLATE(expression, from_string, to_string)

Parameters:

expression: It is a string or character set that will be displayed after translation.
from_string: It is a string, which all occurrences of each character will find in expression
to_string: It is also a string, which will replace all occurrences of from_string.

Note: The argument from_string can contain more characters than the argument to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in expression, then they are removed from the return value. An empty string cannot be used for to_string to remove all characters in from_string from the return value.

Example: The following example replaces all vertical bars with commas.

SELECT TRANSLATE('comma|delimited|text', '|', ',') AS "New String" FROM DUAL;

Output:

Example: The following example, will replace multiple characters.

SELECT TRANSLATE('MAP-SSS-PAM-MAN-NUT-TUB', 'AMST', 'BDFL') AS "New String" FROM DUAL;

Output:

Example: In the below example, a will be replaced with e, the h has no complement and is dropped.

SELECT TRANSLATE('So What', 'ah', 'e') AS "New String" FROM DUAL;

Output:

Example: In the below example, capital A is replaced with capital A. The double quote is eliminated because there is no match.

SELECT TRANSLATE('"Replace Double Quotes "', 'A"', 'A') AS "New String" FROM DUAL;

Output:

Note: The Oracle TRANSLATE function provides functionality related to that provided by the Oracle REPLACE function. The REPLACE lets you substitute a single string for another single string, as well as remove character strings whereas the TRANSLATE function lets you make several single-character, one-to-one substitutions in one operation.

SUBSTR() Function in Oracle:

The SUBSTR function in Oracle is used to return the specified number (substring_length) of characters from a particular position of a given string. While working with Oracle SUBSTR functions, you need to remember the following points.

If the position is 0, then it is treated as 1.
If the position is positive, then Oracle Database counts from the beginning of char to find the first character.
If the position is negative, then Oracle counts backward from the end of char.
If substring_length is omitted, then Oracle returns all characters to the end of char. If substring_length is less than 1, then Oracle returns null.

Following is the syntax to use SUBSTR functions in Oracle.

Syntax: SUBSTR (char, position [, substring_length ])

Parameters:

char: This is the original string from which a substring is to be returned. It can be any data type such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The exceptions are SUBSTRC, SUBSTR2, and SUBSTR4, which do not allow char to be a CLOB or NCLOB.
position: An integer number indicating the string position within the string char. It can be NUMBER, or any data type that can be implicitly converted to NUMBER, and must resolve to an integer.
substring_length: It is an integer indicating the number of characters to be returned. It can be NUMBER, or any data type that can be implicitly converted to NUMBER, and must resolve to an integer.

Note: The return value is the same datatype as char. Floating-point numbers passed as arguments to SUBSTR are automatically converted to integers.

Example:

SELECT SUBSTR('HELLO',2,3) "New String" FROM DUAL;

Output:

Example:

SELECT SUBSTR('WELCOME',4,2) "New String" FROM DUAL;

Output:

Example:

SELECT SUBSTR('WELCOME',-6,3) "New String" FROM DUAL;

Output

Example:

SELECT SUBSTRB('ABCDEFG',5,4.2) "New String" FROM DUAL;

Output:

INSTR() Function in Oracle:

The string INSTR function in Oracle is used to search string for a substring. The function returns an integer indicating the position of the character in the string that is the first character of this occurrence. f a substring that is equal to substring is found, then the function returns an integer indicating the position of the first character of this substring. If no such substring is found, then the function returns zero. Following is the syntax to use string INSTR function in Oracle

Syntax: INSTR(string , substring [, position [, occurrence ] ])

Parameters:

string: This is the original string to search. It can be of any data type such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
substring: This is the substring to search for in the original string. It can be of any data type such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
position: This is Optional Parameter. The position is a nonzero integer number indicating the character of the string from where the search begins. If omitted, it defaults to 1. The first position in the string is 1. If the position is negative, then the INSTR function counts backward from the end of the string and then searches backward from the resulting position. It can be NUMBER or any data type that can be implicitly converted to NUMBER and must resolve to an integer.
occurrence: This is also an Optional parameter. The Occurrence parameter is an integer number indicating which occurrence of a substring in string INSTR function should search for. It can be NUMBER or any data type that can be implicitly converted to NUMBER and must resolve to an integer.

Example:

SELECT INSTR('HELLO WELCOME','O') "New String" FROM DUAL;

Output:

Example:

SELECT INSTR('HELLO WELCOME','Z') "New String" FROM DUAL;

Output:

Example:

SELECT INSTR('HELLO WELCOME','O',1,2) "New String" FROM DUAL;

Output:

Example:

SELECT INSTR('HELLO WELCOME','E',5,2) "New String" FROM DUAL;

Output:

Example:

SELECT INSTR('HELLO WELCOME','E',1,4) "New String" FROM DUAL;

Output:

Example:

SELECT INSTR('HELLO WELCOME','E',-1,3) "New String" FROM DUAL;

Output:

Example:

SELECT INSTR('HELLO WELCOME','L',-4,3) "New String" FROM DUAL;

Output:

Example:

SELECT INSTR('HELLO WELCOME','L',-6,3) "New String" FROM DUAL;

Output:

Note: Position of char’s always fixed either count from left to right (or) right to left.

In the next article, I am going to discuss Date Functions in Oracle with Examples. Here, in this article, I try to explain String Functions in Oracle with Examples and I hope you enjoy this String Functions in Oracle with Examples article.

Dot Net Tutorials

About the Author: Pranaya Rout

Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.