Oracle SQL String Functions: Syntax, Uses, and Examples

Formulas & functions · Updated November 22, 2026

Oracle SQL string functions operate on character values to search, extract, combine, change, and format text. Common choices include SUBSTR and INSTR for finding text, UPPER and LOWER for case conversion, TRIM for removing surrounding characters, and REPLACE for substitutions; the || operator combines strings.

What Oracle SQL string functions do

String functions are SQL expressions that take character data as input and return a value, often another character value. They can be used in a SELECT list, a WHERE condition, an ORDER BY clause, or other expressions. For example, applying UPPER to a customer name can produce a normalized display value, while using INSTR can help locate a delimiter in an imported field.

Oracle provides functions for several distinct jobs. Knowing the job first is more useful than memorizing a long list of names:

  • Change case: UPPER, LOWER, and INITCAP.
  • Measure or locate: LENGTH and INSTR.
  • Extract or combine: SUBSTR, CONCAT, and the concatenation operator ||.
  • Clean or reshape: TRIM, LTRIM, RTRIM, REPLACE, and TRANSLATE.
  • Format or match patterns: LPAD, RPAD, and the regular-expression functions.

Most examples below use character literals, but the same functions can usually work with character columns. A function applied to a column changes the value returned by that expression; it does not update the stored row unless the result is used in an UPDATE.

Choosing the right function

Start by describing the operation precisely. If you need the position of a substring, use INSTR; if you need the characters at that position, use SUBSTR. If you want to remove ordinary spaces from the ends, use TRIM. If you need to replace a known literal sequence anywhere in a value, use REPLACE. Regular expressions are useful when the pattern varies, but they are not automatically the best choice for a simple fixed substitution.

A basic query shows how function calls fit into ordinary SQL:

SELECT UPPER('Oracle sql') AS normalized_text,
       LENGTH('Oracle sql') AS character_count
FROM dual;

In this example, the returned text is ORACLE SQL and the length is 10, counting the space. Oracle’s DUAL table is a convenient one-row table often used for evaluating expressions. With a real table, replace dual with the table name and use the relevant column instead of the literal.

Changing case and comparing text

UPPER converts letters to uppercase, LOWER converts them to lowercase, and INITCAP capitalizes the first letter of each word according to its rules. For example, LOWER('NORTH REGION') returns north region. These functions are often used to create a consistent search expression when source data has inconsistent capitalization.

For a case-insensitive comparison, a common pattern is to apply the same conversion to both sides:

SELECT customer_id, customer_name
FROM customers
WHERE UPPER(customer_name) = UPPER('maria lopez');

This can find names whose stored capitalization differs from the search text. It does not remove accents, extra spaces, or other differences. Also consider performance: applying a function to a column can affect whether an ordinary index on that column can be used for the lookup. If this is a frequent query, check the execution plan and consider an appropriate function-based index or a consistently stored normalized value, following the database’s design rules.

Case handling can depend on language and collation settings, particularly for characters outside basic English. Do not assume that changing case is equivalent to every form of linguistic, accent-insensitive, or locale-aware comparison. Test representative characters under the database settings used by the application.

Finding and extracting text

INSTR returns the position of a substring, and SUBSTR returns part of a string. In Oracle, positions ordinarily start at 1. A start position of 0 for SUBSTR is treated as 1; a negative start position counts backward from the end. If the requested substring is not found, INSTR returns 0, which is useful in conditions.

SELECT SUBSTR('Database', -4) AS ending,
       INSTR('banana', 'an', 1, 2) AS second_match
FROM dual;

The first expression returns base. The second returns 4, the starting position of the second occurrence of an. The full form of INSTR allows a start position and an occurrence number, so you can search for later matches rather than only the first one. The corresponding SUBSTR form accepts a starting position and an optional length.

These functions are handy for simple delimiter-based data, such as extracting the part before or after a known separator. However, nested or optional separators can make a long combination of INSTR and SUBSTR difficult to maintain. For a variable pattern, REGEXP_SUBSTR may be clearer. For structured data, use a suitable data type or parsing method instead of relying on string positions that can break when the input format changes.

Joining strings together

Oracle’s || operator concatenates character expressions and is the usual readable choice when combining several pieces. CONCAT also joins strings, but it accepts two arguments, so multiple calls may be needed for three or more pieces.

SELECT first_name || ' ' || last_name AS full_name,
       CONCAT('Order ', order_number) AS label
FROM orders;

The separator in the first expression is an ordinary string literal, so it appears between the two names. Add separators deliberately: concatenating two values without one can turn Ann and Lee into AnnLee. Oracle treats a zero-length character string as NULL; concatenation is a notable special case in which concatenating a null or zero-length string with a non-null string yields the other string. Do not generalize that behavior to every SQL function or operator, since null handling varies by operation.

Trimming, padding, and formatting

TRIM removes specified characters from the beginning, end, or both ends of a value. With no explicit trim character, it removes spaces. LTRIM and RTRIM trim from the left and right, respectively. The optional trim argument is a set of characters, not necessarily one exact sequence: LTRIM('00042', '0') removes leading zero characters and returns 42.

SELECT TRIM(BOTH '-' FROM '---ready---') AS cleaned,
       LPAD('7', 3, '0') AS padded
FROM dual;

Here, cleaned is ready, and padded is 007. LPAD adds characters on the left and RPAD adds them on the right to reach a requested display length. Padding is useful for presentation or fixed-width output, but it should not be confused with numeric formatting; a value that looks like a number is still character data after string operations. Padding can also be truncated to the requested length, so choose a length large enough for the original value.

Replacing characters and sequences

REPLACE substitutes every occurrence of a literal search string with a replacement string. For example, REPLACE('2026-11-03', '-', '/') returns 2026/11/03. If the replacement is omitted, matching occurrences are removed. Use this for known text substitutions, such as changing a separator or removing a fixed unwanted token.

TRANSLATE works at the character level rather than replacing a multi-character sequence. It maps each character in one set to the character at the corresponding position in another set. This can be useful for several one-character substitutions in one expression, but it is easy to misread because the mapping is positional. When the task is a normal word or substring replacement, REPLACE is usually clearer.

These operations do not validate whether the resulting text is meaningful. Replacing hyphens in a date-looking string does not convert it into an Oracle DATE. If an application needs date arithmetic, store and process dates as date values, then format them for display with the appropriate date conversion function.

Regular-expression string functions

Oracle’s regular-expression family supports pattern-based searches and substitutions. Useful members include REGEXP_LIKE for testing a condition, REGEXP_SUBSTR for extracting a match, REGEXP_INSTR for locating a match, and REGEXP_REPLACE for replacing matched text. For instance, REGEXP_SUBSTR('order 8472', '[0-9]+') returns 8472, because the pattern matches one or more digits.

SELECT REGEXP_REPLACE('Call 555-0182', '[^0-9]', '') AS digits_only
FROM dual;

The expression removes characters that are not digits, returning 5550182. Regular-expression patterns have their own syntax, and Oracle’s supported behavior should be checked in the documentation for the database version in use. Patterns are powerful, but they can be harder to read and may cost more to evaluate than a straightforward literal operation. Use the simplest function that expresses the requirement, and test inputs that are empty, malformed, or unusually long.

Lengths, character sets, and null behavior

LENGTH returns a character length, while LENGTHB returns a length in bytes. They can differ when a character uses multiple bytes in the database character set. Oracle also provides length functions tied to different character semantics, including LENGTHC, LENGTH2, and LENGTH4. For most everyday text checks, LENGTH is the natural starting point; byte limits and multilingual text require more care.

Oracle treats an empty character string written as '' as NULL for VARCHAR2 behavior. This matters because many functions return null when given a null input, and comparisons involving null do not behave like comparisons to an ordinary value. Use IS NULL to test for null rather than writing = NULL. Fixed-width CHAR values also have padding and comparison behavior that can surprise readers who expect them to act exactly like VARCHAR2.

Text may also be stored in different character sets or use different length semantics. If a function result is assigned to a constrained column or variable, verify both its character and byte requirements where relevant. A string that fits a character count is not necessarily guaranteed to fit a particular byte limit.

Common mistakes and practical guidance

Many string errors come from assumptions about positions, whitespace, or data types rather than from complicated SQL. Check whether positions begin at 1, whether a search can fail, and whether the input might be null. Remember that TRIM removes end characters, not every matching character inside a string; use REPLACE when internal occurrences must also go.

Before using a string function in a production query, consider these checks:

  • Test normal values as well as null, empty, missing-delimiter, and unexpected-format values.
  • Decide whether the requirement is about characters, bytes, literal text, or a variable pattern.
  • Keep searchable columns unwrapped where possible, or verify indexing and execution plans when a function is necessary.
  • Separate formatting from data conversion; text that resembles a date or number is not automatically that data type.
  • Use explicit aliases and readable expressions when several functions are nested.

A small, concrete query is often the best way to confirm edge behavior on the target Oracle version and character set. For more complex parsing, document the expected input format and decide what should happen when that format is violated.

Frequently Asked Questions

What is the Oracle SQL function for combining strings?
The || operator is the common choice, for example first_name || ' ' || last_name. CONCAT also combines strings but takes two arguments.

How do I get part of a string in Oracle?
Use SUBSTR(value, start_position, length). Positions normally start at 1, and a negative start position counts from the end of the value.

How do I find text inside a string?
Use INSTR(value, search_text). It returns the position of a match, or 0 if the text is not found; optional arguments let you set a starting position and occurrence number.

How do I remove spaces from a string in Oracle?
Use TRIM(value) to remove spaces from both ends, or LTRIM and RTRIM for one side. These functions do not remove spaces between words.

What is the difference between REPLACE and TRANSLATE?
REPLACE substitutes a literal substring, while TRANSLATE maps individual characters by position between two character sets. Use REPLACE for ordinary word or sequence substitutions.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.