Data Manipulation   «Prev  Next»

Lesson 2 Character (string) functions
Objective List character (string) functions unique to Oracle or different in Oracle.

Oracle Character (string) functions

SQL provides built-in functions for common data manipulation. Some of these built-in functions include:
  1. Concatenation: Put two fields, literals, or expressions together into a single field.
  2. Padding: Add a character, such as a dash or a blank, to the beginning or end of a field to make it longer.
  3. Trimming: Pull out a portion of the data from a column, such as the first ten characters from a 30-character field.
SQL functions that operate on character fields (also called strings) are useful for many tasks, such as:
  1. Formatting data for output in reports, form letters, or address labels where the database column data is not stored exactly the way you want the output to appear
  2. Comparing a portion of the data in a column to an expression or literal; comparisons can be part of a WHERE clause of a query, or part of an IF statement in PL/SQL
  3. Converting data to a different format
  4. Replacing one character of the data with a different character, such as replacing a blank space with a hyphen

Character Functions

The table below lists all the character functions available in Oracle and what they do.

Function Syntax Description
CONCAT or || CONCAT(col1, col2) or Col1 || col2 Concatenates one column with another
INITCAP INITCAP(col1) Capitalizes first letter of each word
INSTR INSTR(col1, c) Returns the position of (c) in col1
LENGTH LENGTH(col1) Returns the length of col1
LOWER LOWER(col1) Converts all letters to lower case
LPAD LPAD(col1, n, c) Add character (c) onto the left side of col1, repeating it until col1 is (n) characters long. You can list multiple characters in (c).
LTRIM LTRIM(col1 [,set]) Remove characters listed in (set) from the left side of col1 until a character that is not in the set is found. Default for (set) is a blank space.
REPLACE REPLACE(col1, old, new) Replace all occurrences of the old character with the new character. You can include multiple characters in the (old) and (new) parameters; similar to TRANSLATE.
RPAD RPAD(col1, n, c) See LPAD; this is the same except that it adds onto the right side of col1.
RTRIM RTRIM(col1 [,set]) See LTRIM; this is the same except that it works its way from the left to right.
SOUNDEX SOUNDEX(col1) Convert col1 to a phonetic version of the word; allows you to compare words that sound alike but are spelled differently.
SUBSTR SUBSTR(col1, m [,n]) Returns a part of col1; start at character in position (m) and continue for a length of (n)
TRANSLATE TRANSLATE(col1, old, new) Changes old to new in col1
UPPER UPPER(col1) Converts col1 to upper case


Oracle REPLACE function Purpose

REPLACE(string1, match_string, replace_string)
Returns a string in which all occurrences of match_string in string1 are replaced by replace_string. REPLACE is useful for searching a pattern of characters, and then changing all instances of that pattern in a single function call.

REPLACE returns char 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. If search_string is null, then char is returned. Both search_string and replacement_string, as well as char, can be any of the datatypes
  1. CHAR,
  2. VARCHAR2,
  3. NCHAR,
  4. NVARCHAR2,
  5. CLOB, or
  6. NCLOB.

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. 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
Example: The following example replaces occurrences of J with BL: 
SELECT REPLACE('JACK and JUE','J','BL') "Changes"
     FROM DUAL;

Changes
--------------
BLACK and BLUE

In the next lesson, you will learn the use of the character functions CONCAT, SUBSTR, LENGTH, and INSTR.

Question: Is the "Oracle REPLACE function" unique to PL/SQL?
No, the `REPLACE` function is not unique to PL/SQL. It is a standard SQL function that is available in most SQL databases, including Oracle, MySQL, PostgreSQL, and Microsoft SQL Server. The specific syntax and behavior of the function may vary slightly between different databases, but the basic functionality of replacing occurrences of a substring with another substring is generally the same.
Here are some examples of how the `REPLACE` function is used in different databases: Oracle 
SELECT REPLACE('Hello, world!', 'world', 'universe') FROM DUAL;

MySQL 
SELECT REPLACE('Hello, world!', 'world', 'universe') FROM DUAL;

PostgreSQL 
SELECT REPLACE('Hello, world!', 'world', 'universe');

Microsoft SQL Server 
SELECT REPLACE('Hello, world!', 'world', 'universe');

As you can see, the basic structure of the `REPLACE` function is similar across all of these databases. The first argument is the string to be searched, the second argument is the substring to be replaced, and the third argument is the replacement string.

SEMrush Software