Oracle Character String Functions

Character functions let a query combine, inspect, search, extract, replace, pad, trim, or change the case of text. They are useful when producing names, mailing labels, identifiers, report headings, search expressions, and other results that do not have the same presentation as the stored columns. Oracle AI Database 26ai supplies familiar SQL functions together with Oracle syntax, datatype rules, globalization features, and alternate length semantics that must be considered when an application handles multilingual text.

The functions in this lesson are single-row functions. Oracle evaluates each expression for an applicable input row and returns a result for that evaluation. Applying UPPER(last_name) in a query does not update last_name; it returns an uppercase value to the query. A separate data-changing statement would be required to alter the stored column.

Some character functions return another character value. Examples include UPPER, REPLACE, SUBSTR, and RTRIM. Others inspect character input but return a number. LENGTH returns a length, while INSTR returns the position at which a search string is found. Knowing the return datatype is essential when functions are nested, compared, sorted, or passed to another expression.

Character-Function Families

Oracle provides many character functions, but they are easier to learn when organized by task. The following table introduces the families used in this module. It does not imply that every function is unique to Oracle; several are standard or widely available, while Oracle supplies distinctive variants or behavior.

Applying Several Functions in One Query

The following query demonstrates representative functions without changing any table data:

SELECT INITCAP('oracle sql functions')          AS heading,
       LPAD('42', 5, '0')                      AS padded_code,
       REPLACE('east region', 'east', 'north') AS revised_region,
       SUBSTR('INV-2026-0042', -4)             AS sequence_text,
       INSTR('department:finance', ':')        AS delimiter_position
FROM   dual;

The expressions return Oracle Sql Functions, 00042, north region, 0042, and 11. INITCAP changes the case of the words. LPAD extends a value to a requested display length. REPLACE substitutes one string for another. A negative SUBSTR position counts backward from the end of the source, and INSTR reports a one-based position. If INSTR does not find the search string, it returns zero rather than null.

These transformations can also be applied to table columns. In the following query, the alias labels the returned expression; the stored employee name remains unchanged:

SELECT employee_id,
       UPPER(last_name) AS display_last_name
FROM   employees
ORDER  BY employee_id;

Characters, Bytes, and Unicode Length Semantics

A displayed symbol is not always represented by one byte or one Unicode code point. Oracle therefore provides related LENGTH, INSTR, and SUBSTR functions with different length semantics. Choosing the wrong form can split an encoded character or produce a position that does not match the application's definition of a character.

The unsuffixed functions follow the input datatype and its character set. Consequently, apparently identical values stored with different character datatypes can have different semantics. The numbered forms are appropriate only when an interface or specification explicitly requires UCS-2 or UCS-4 units. Byte forms should be selected only when the requirement is genuinely defined in bytes.

On an AL32UTF8 database, the following query illustrates the difference between characters and bytes. The word contains seven characters, but ß occupies two bytes in AL32UTF8, so the byte count is eight:

SELECT LENGTH('Fußball')  AS character_count,
       LENGTHB('Fußball') AS byte_count
FROM   dual;

The character-set assumption is important. A byte result should not be copied into documentation as though it were independent of the database character set. For ordinary names and natural-language text, character semantics are usually more meaningful than byte semantics.

Complete Characters in Oracle AI Database 26ai

Oracle AI Database 26ai enhances complete-character processing. INSTRC, LENGTHC, and SUBSTRC count an Ideographic Variation Sequence as one complete character. Oracle also applies this treatment to a character with the Unicode Mn general property when it follows its base character. The related LIKEC condition uses the same complete-character concept for pattern matching.

Oracle documents the change with a string containing an ideographic character followed by a variation selector:

SELECT LENGTHC(UNISTR('My IVS: \845B\DB40\DD00')) AS complete_characters
FROM   dual;

The query returns 9 in Oracle AI Database 26ai; earlier releases returned 10. This change matters when an application must avoid separating variation sequences or combining marks from their base characters. It does not mean that every application should automatically replace the ordinary functions with their C variants. The correct choice follows the data and its Unicode requirements.

Datatype support must also be verified for each variant. For example, ordinary SUBSTR accepts character and national-character values, including CLOB and NCLOB. However, SUBSTRC, SUBSTR2, and SUBSTR4 do not accept CLOB or NCLOB input. A family resemblance does not guarantee identical datatype support.

Case Conversion and Linguistic Behavior

The ordinary UPPER, LOWER, and INITCAP functions use binary case mapping defined for the underlying character set. INITCAP capitalizes the first letter of each word and converts the remaining letters to lowercase. It treats whitespace and non-alphanumeric characters as word boundaries.

That behavior can be useful for headings, but it is not a universal name-normalization rule. Acronyms, brand names, personal names, identifiers, and language-specific capitalization may not match the result produced automatically. When linguistic-sensitive case conversion is required, Oracle provides NLS_UPPER, NLS_LOWER, and NLS_INITCAP. Even those functions do not replace validation against the business domain's authoritative spelling.

Padding, Trimming, and Substitution

LPAD and RPAD return a value padded to a requested display length. The padding argument can contain more than one character; Oracle repeats its pattern as needed. If the source is longer than the requested length, the returned value is shortened to fit. Padding is therefore not merely a promise to add characters and should be tested with overlength input.

Trimming is different from substring extraction. LTRIM and RTRIM remove characters found in a specified set from the left or right edge until a character outside that set is reached. If the set is omitted, it defaults to a single blank. SUBSTR, by contrast, returns a portion based on a position and optional length.

REPLACE substitutes every occurrence of a search string with another string. Omitting the replacement removes the matched substring. TRANSLATE performs a different operation: it maps individual characters in one set to the characters at corresponding positions in another set. Use REPLACE for substring substitution and TRANSLATE when the requirement is a character-by-character mapping.

Oracle documents REPLACE for CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, and NCLOB arguments. If the first argument is a LOB, the function returns a CLOB; otherwise it returns VARCHAR2. Other character functions have their own argument and return rules, so LOB support should never be inferred from one example.

Phonetic and Pattern-Oriented Searching

SOUNDEX returns a phonetic code intended for English-language comparison. It can identify some similar spellings, such as Smith and Smyth, but it is not a general fuzzy-search algorithm and is unsuitable as a universal multilingual matcher. Its results should be treated as candidates that may require additional confirmation.

Oracle's regular-expression functions support more complex pattern tasks. REGEXP_INSTR returns a match position, REGEXP_SUBSTR returns matching text, REGEXP_REPLACE substitutes matches, and REGEXP_COUNT counts occurrences. Regular expressions are powerful, but a simpler character function is usually clearer when it directly expresses the requirement.

Nulls, Predicates, and Indexes

Oracle SQL currently treats a zero-length character string as null. Character expressions should therefore be tested with missing values as well as ordinary text. Concatenating address components, for example, does not automatically decide whether punctuation and spaces should appear when a component is null. Those business presentation rules must be written explicitly. Module 4 returns to null-handling expressions in Lesson 9.

Functions are also commonly used in predicates, but transforming a table column can affect use of an ordinary index. A condition such as WHERE UPPER(last_name) = 'SMITH' may require an appropriate function-based index or another case-insensitive design to provide the desired access path. This does not make functions inappropriate in search conditions; it means that correctness and physical design should be considered together.

Summary

• Character functions can return transformed text or numeric information such as a length or position.
• Choose functions by task: combining, case conversion, measurement, searching, extraction, padding, trimming, substitution, or matching.
• The B, 2, 4, and C variants use byte, UCS-2, UCS-4, and Unicode complete-character semantics, respectively.
• Oracle AI Database 26ai counts IVS and qualifying combining-mark sequences as one complete character in the ...C functions.
• Verify datatype, LOB, null, collation, and return-value behavior for the specific function rather than generalizing from its family.
• Use English-oriented SOUNDEX and powerful regular expressions only where their behavior matches the actual requirement.

The next lesson examines CONCAT, the concatenation operator, LENGTH, and INSTR in greater detail, including their syntax and practical use in Oracle SQL expressions.