Character Functions in Oracle SQL

Topic Introduction: Character Functions in Oracle SQL which manipulate with character or varchar/char data. Single-row character functions accept character data as input and can return both character and numeric values.

Character functions can be divided into the following:

1. Case-conversion functions
2. Character-manipulation functions

Case-Conversion Functions: LOWER, UPPER, and INITCAP are the three case-conversion functions.

LOWER 

Purpose: Converts mixed-case or uppercase character strings to lowercase

Structure: LOWER(column|expression)

Example1: Select LOWER('OracleSchoolBD') from dual;

Result: oracleschoolbd

UPPER

Purpose: Converts mixed-case or lowercase character strings to uppercase

Structure: UPPER(column|expression)

Example1: Select UPPER('OracleSchoolBD') from dual;

Result: ORACLESCHOOLBD

INITCAP

Purpose: Converts the first letter of each word to uppercase and the remaining letters to lowercase

Structure: INITCAP(column|expression)

Example1: Select INITCAP('OracleSchoolBD') from dual;

Result: Oracleschoolbd

Character-Manipulation Functions: CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, and TRIM are the character-manipulation.

CONCAT

Purpose: Joins values together (You are limited to using two parameters with CONCAT.). Concatenates the first character value to the second character value; equivalent to concatenation operator (||).

Structure: CONCAT(column1|expression1,column2|expression2)

Example1: Select CONCAT('Oracle', 'SchoolBD') from dual;

Result: OracleSchoolBD

SUBSTR 

Purpose: Extracts a string of determined length. Returns specified characters from character value starting at character position m, n characters long (If m is negative, the count starts from the end of the character value. If n is omitted, all characters to the end of the string are returned.)

Structure: SUBSTR(column|expression,m[,n])

Example1: Select SUBSTR('OracleSchoolBD',1,6) from dual;

Result: Oracle

LENGTH 

Purpose: Shows the length of a string as a numeric value. Returns the number of characters in the expression

Structure: LENGTH(column|expression)

Example1: Select LENGTH('OracleSchoolBD') from dual;

Result: 14

INSTR

Purpose: Finds the numeric position of a named character. Returns the numeric position of a named string. Optionally, you can provide a position m to start searching, and the occurrence n of the string. m and n default to 1, meaning start the search at the beginning of the string and report the first occurrence.

Structure: INSTR(column|expression,’string’, [,m], [n] )

Example1: Select INSTR('OracleSchoolBD', 'S') from dual;

Result: 7

LPAD

Purpose: Returns an expression left-padded to the length of n characters with a character expression

Structure: LPAD(column|expression, n,'string')

Example1: Select LPAD('OracleSchoolBD',18,'*') from dual;

Result: ****OracleSchoolBD

RPAD

Purpose: Returns an expression right-padded to the length of n characters with a character

expression

Structure: RPAD(column|expression, n,'string') 

Example1: Select RPAD('OracleSchoolBD',18,'*') from dual;

Result: OracleSchoolBD****

REPLACE

Purpose: Searches a text expression for a character string and, if found, replaces it with a specified replacement string

Structure: REPLACE(text,search_string,replacement_string)

Example1: Select REPLACE('OracleSchoolBD', 'BD', 'Bangladesh') from dual;

Result: OracleSchoolBangladesh

TRIM

Purpose: Trims leading or trailing characters (or both) from a character string (If trim_character or trim_source is a character literal, you must enclose it within single quotation marks.)

Structure: TRIM(leading|trailing|both,trim_character FROMtrim_source)

Example1: Select TRIM('BD' FROM 'OracleSchoolBD') from dual;

Result: OracleSchool