How to Replace a String with another String in Oracle?

String in Oracle

Variable-length strings (VARCHAR2) and fixed-length strings (CHAR) are the two main character datatypes that Oracle offers. When you enter a number that is less than the specified length of a CHAR column, Oracle automatically adds spaces to it. It is typically advised to avoid using VARCHAR because its behaviour may change in future Oracle versions and instead use VARCHAR2 for variable-length character string fields.

The following are important string functions:

LOWER, UPPER, and INITCAP (Capitalise First Letter)

To control the case of characters in a string, utilise these functions.

1. LOWER(string): All letters in a string are converted to lowercase using the LOWER(string) function.
2. UPPER(string): All letters in a string are converted to uppercase using the UPPER(string) function.
3. INITCAP(string): Each word in a string is capitalised, and the remaining letters are changed to lowercase using the INITCAP(string) function. Usually, spaces or non-alphanumeric characters are used to separate words.

For example, if you have a city name stored in uppercase and want to display it with normal capitalisation:

SELECT
    City,
    UPPER(City) AS "UPPERCASE_CITY",
    LOWER(City) AS "lowercase_city",
    INITCAP(LOWER(City)) AS "InitCap_City"
FROM
    WEATHER
WHERE City = 'LIMA';

Output:

CITY        UPPERCASE_CITY LOWERCASE_CITY INITCAP_CITY
----------- -------------- -------------- ------------
LIMA        LIMA           lima           Lima

For names containing internal capitalisation, such as “Mcwilliams,” INITCAP may not always yield the desired result. NLS_LOWER, NLS_UPPER, and NLS_INITCAP are additional options provided by Oracle for linguistically sensitive case conversions based on National Language Support (NLS) values.

LENGTH (Get String Length)

A string’s total character count, including letters, spaces, and other symbols, can be found using the LENGTH(string) function. It helps with WHERE and ORDER BY clauses and for figuring the space needs.

SELECT
    Name,
    LENGTH(Name) AS "Name_Length"
FROM
    MAGAZINE
WHERE Name = 'PSYCHOLOGICA';

Output:

NAME         NAME_LENGTH
------------ -----------
PSYCHOLOGICA          12

Since Oracle regards empty strings as NULL, keep in mind that LENGTH normally returns NULL for an empty string. If the CLOB contains zero bytes but is not NULL, then LENGTH may return zero. This is the exception for CLOB data. LENGTHB, LENGTHC, LENGTH2, and LENGTH4 are variations that use bytes, Unicode full characters, UCS2 codepoints, and UCS4 codepoints, respectively, to determine length. With the LONG datatype, you are unable to perform LENGTH on columns.

SUBSTR (Extract Substring)

To extract a section of a string, use the SUBSTR(string, start [,count]) function. It begins with a set character position (start) and goes on for a predetermined number of characters (count). It extracts the string from beginning to end if count is left empty.

1. A string that start with a positive value counts from the beginning.
2. A negative start value counts from the string’s end backwards.

SELECT
    SUBSTR('ABCDEFG', 3, 4) AS "Substring_Positive",
    SUBSTR('ABCDEFG', -5, 4) AS "Substring_Negative"
FROM
    DUAL;

Output:

SUBSTRING_POSITIVE SUBSTRING_NEGATIVE
------------------ ------------------
CDEF               CDEF

The starting position computation may be impacted if you use a negative start with CHAR datatype columns since their values are padded with spaces to their full length. Like LENGTH, there are byte- and Unicode-aware variants of SUBSTR, such as SUBSTRB, SUBSTRC, SUBSTR2, and SUBSTR4.

INSTR (Find Substring)

A string can be searched for a certain set of characters using the INSTR(string, set [,start [,occurrence]]) function, which then returns the beginning position of the match.

• start: The starting location for the search (by default, it is 1).
• occurrence: The set occurrence (by default, 1) to look for.

SELECT
    Author,
    INSTR(Author, 'O') AS "First_O_Position",
    INSTR(Author, 'O', 1, 2) AS "Second_O_Position"
FROM
    MAGAZINE
WHERE Author = 'BONHOEFFER, DIETRICH';

Output:

AUTHOR                 FIRST_O_POSITION SECOND_O_POSITION
---------------------- ---------------- -----------------
BONHOEFFER, DIETRICH                  2                14

INSTR is more flexible than the LIKE operator, as it can be used anywhere except the FROM clause, whereas LIKE is restricted to WHERE or HAVING clauses. INSTR also has REGEXP_INSTR for regular expression searches.

CONCAT (or || for Concatenation)

Two or more strings can be joined together by concatenation.

1. For concatenation, the most popular and frequently chosen approach is the || operator.
2. Although it can only connect two strings at a time, the CONCAT(string1, string2) method does the same thing and frequently necessitates nesting for more than two strings.

SELECT
    City || ', ' || Country AS "City_Country"
FROM
    LOCATION
WHERE City = 'SYDNEY';

Output:

CITY_COUNTRY
-------------------
SYDNEY, AUSTRALIA

The non-NULL parameter is returned when concatenating with NULL values, and the NULL argument is disregarded. NULL is the outcome if both are NULL.

REPLACE

The REPLACE(char, search_string [,replace_string]) function replaces all occurrences of search_string within char with replace_string. If replace_string is omitted or NULL, all occurrences of search_string are removed. This differs from TRANSLATE, which performs character-by-character substitution.

SELECT
    REPLACE('GEORGE', 'GE', 'EG') AS "Replaced_String",
    REPLACE('GEORGE', 'GE', NULL) AS "Removed_String"
FROM
    DUAL;

Output:

REPLACED_STRING REMOVED_STRING
--------------- --------------
EGOREG          OR

To replace patterns with regular expressions, Oracle Database 10g introduced REGEXP_REPLACE.

TRIM (LTRIM, RTRIM)

The endpoints of a string are stripped of certain characters by these functions.

• LTRIM(string [, ‘set’]): Eliminates every instance of any character in ‘set’ from the string left side. By default, spaces are removed if ‘set’ is not given.
• RTRIM(string [, ‘set’]) Eliminates every instance of any character in ‘set’ from the string right side. By default, spaces are removed if ‘set’ is not given.
• TRIM([ [LEADING | TRAILING | BOTH] [trim_character] FROM ] string): This ISO SQL standard-compliant function can eliminate a single provided trim_character from a string’s leading, trailing, or both ends. By default, BOTH is used. It eliminates gaps if trim_character is left unspecified.

SELECT
    LTRIM('<===>HELLO<===>', '=<>') AS "LTrim_Example",
    RTRIM('<===>HELLO<===>', '=<>') AS "RTrim_Example",
    TRIM(BOTH '.' FROM '.....Hi there!.....') AS "Trim_Both",
    TRIM(LEADING '.' FROM '.....Hi there!.....') AS "Trim_Leading"
FROM
    DUAL;

Output:

LTRIM_EXAMPLE RTRIM_EXAMPLE TRIM_BOTH   TRIM_LEADING
------------- ------------- ----------- ------------
HELLO<===>    <===>HELLO    Hi there!   Hi there!.....

TRIM is intended to delete a single specified character, whereas LTRIM and RTRIM can remove any character from a set you supply. This is the main difference.

These features, especially when combined, provide strong formatting, cleaning, and character data manipulation capabilities in Oracle databases. Effective SQL querying and data management require an understanding of each component’s unique capabilities as well as how they can be chained or nested together.

With the help of these string methods, you may precisely shape raw text data into the format you require, much way a chef employs different knives to chop, slice, and trim ingredients to perfection.