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

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

The `REPLACE` function is not unique to PL/SQL. It is a built-in SQL function in Oracle Database and can be used in both SQL statements and PL/SQL code.
Overview of `REPLACE` Function: The `REPLACE` function is used to substitute all occurrences of a specified substring in a string with another substring. Its syntax is:

"
REPLACE(string, search_string, replacement_string)

Key Points:

• Availability in SQL: You can use REPLACE in a standard SQL query. For example:

  SELECT REPLACE('Oracle Database', 'Database', 'DB') FROM DUAL;
  -- Output: Oracle DB
      

• Availability in PL/SQL: You can also use REPLACE in PL/SQL blocks, procedures, and functions. For example:

  DECLARE
      result VARCHAR2(100);
  BEGIN
      result := REPLACE('PL/SQL is great!', 'great', 'awesome');
      DBMS_OUTPUT.PUT_LINE(result);
  END;
  -- Output: PL/SQL is awesome!
    

• Cross-Version Support: The REPLACE function has been supported in Oracle Database across multiple versions, not limited to any specific context (SQL or PL/SQL).

Conclusion The `REPLACE` function is not exclusive to PL/SQL. It is part of Oracle's SQL language features and can be used in both SQL statements and PL/SQL code. This versatility makes it a powerful tool for string manipulation in Oracle databases.
In the next lesson, you will learn the use of the character functions CONCAT, SUBSTR, LENGTH, and INSTR.