Oracle String Functions Explained with Examples

Formulas & functions · Updated November 22, 2026

Oracle string functions operate on character data in SQL and PL/SQL. For everyday work, use functions such as SUBSTR to extract text, INSTR to find it, REPLACE to change it, and TRIM to remove surrounding characters; Oracle also supports the || operator for concatenation.

What Oracle string functions do

A string function takes character data as input and returns a value based on that text. You can use one in a query’s select list, a filter, a join condition, an ordering expression, or an update. For example, a report might display a customer’s name in uppercase, extract a code from a longer value, or compare email addresses without regard to letter case.

Oracle’s character functions work with values such as CHAR, VARCHAR2, NCHAR, and NVARCHAR2. Some functions have byte-oriented variants or special rules for Unicode characters, so the apparent length of text is not always the same as its storage size. Function results can also depend on the input data type and the database’s character set or collation settings.

For a simple example, assume a table called customers has a full_name column. SELECT UPPER(full_name) FROM customers; returns an uppercase version of each name without changing the stored value. To change stored data, use an appropriate UPDATE statement and consider transaction and backup practices separately.

Joining text with CONCAT and the || operator

Use || to join text values. Oracle also provides CONCAT(char1, char2), which takes two arguments. The operator is often more convenient when joining several pieces together, while CONCAT can make a two-value operation explicit.

For example, with first_name and last_name columns, SELECT first_name || ' ' || last_name AS display_name FROM customers; creates a display name with a space between the values. The concatenation expression does not insert separators automatically; include a space, comma, or other delimiter explicitly.

Oracle has a notable null behavior: concatenating a non-null value with a null or zero-length string generally returns the non-null value. If both operands are null, the result is null. This differs from some other SQL systems, so do not assume that a missing name component will make the entire concatenated result null. Use NVL or COALESCE when you need a specific replacement for missing data, and decide whether adding a separator in that case is appropriate.

Finding and extracting text with INSTR and SUBSTR

INSTR searches for one string inside another and returns the position of a match. Its basic form is INSTR(string, substring [, position [, occurrence]]). Positions start at 1. The optional position sets where the search begins; a negative position searches backward from the end. The occurrence argument asks for the first, second, or later match. If no match is found, INSTR returns 0.

SUBSTR extracts part of a string: SUBSTR(string, start [, length]). A start value of 1 means the first character. A start value of 0 is treated as 1, and a negative start counts backward from the end. If the requested length is less than 1, the function returns null. For example, SUBSTR('INV-2026-045', 5, 4) returns 2026.

These functions can be combined to extract text around a delimiter. If column file_name contains report.csv, SUBSTR(file_name, 1, INSTR(file_name, '.') - 1) returns report. This expression assumes a period exists; if it does not, INSTR returns 0 and the calculated length is negative. Protect such expressions with a CASE condition or another deliberate fallback when the input format is not guaranteed.

Measuring string length correctly

LENGTH returns the length of a character value in characters as defined for that function and input character set. It counts trailing spaces in a value. Oracle also offers LENGTHB, which returns a length in bytes, and LENGTHC, LENGTH2, and LENGTH4, which use different definitions related to Unicode characters and code points.

That distinction matters when validating limits. A database column’s declared character or byte semantics, the database character set, and the particular function all affect what a length means. A word containing accented or supplementary Unicode characters may occupy more bytes than ordinary ASCII text. If a requirement says a field must fit within a byte limit, use a byte-oriented check such as LENGTHB; if it specifies characters, confirm the column definition and use the appropriate character-based measure. Do not treat the two as interchangeable.

Cleaning text with TRIM, REPLACE, and TRANSLATE

TRIM removes leading characters, trailing characters, or both. With no trim character specified, it removes blank spaces. For instance, TRIM(' Oracle ') returns Oracle. The related LTRIM and RTRIM remove characters from the left or right side. Their second argument is a set of characters to remove, not a complete substring. Thus LTRIM('xxOracle', 'x') removes the leading x characters.

REPLACE(char, search_string [, replacement_string]) replaces every occurrence of a search string. If the replacement argument is omitted, matching text is removed. For example, REPLACE('555-0182', '-', '') returns 5550182. It is a straightforward option for replacing a known literal sequence, but it does not interpret patterns such as “any digit.”

TRANSLATE performs character-by-character mapping. It pairs characters in a source set with characters in a replacement set; source characters without a corresponding replacement character are removed. This is useful for simple character substitutions, but it is not a regular-expression engine and cannot express a variable-length pattern. Choose the function whose behavior matches the task rather than stacking several replacements without checking the result.

  • Use TRIM for unwanted characters at the ends of a value.
  • Use REPLACE for a literal sequence anywhere in the value.
  • Use TRANSLATE for simple one-character mappings or removals.

Changing case and padding values

UPPER converts text to uppercase, LOWER converts it to lowercase, and INITCAP capitalizes the first letter of each word according to Oracle’s rules. For a case-insensitive comparison, a query can compare normalized expressions, such as LOWER(email) = LOWER(:email_value). The bind variable notation shown is commonly used by clients to supply a value safely; the exact binding interface depends on the application.

Case conversion is not the same as linguistic sorting or every possible form of Unicode case folding. If comparisons must follow language-specific rules, check the database’s collation and linguistic settings rather than assuming that UPPER alone resolves every locale-specific issue. Applying a function to a column in a condition can also affect whether an ordinary index is useful; database design and execution plans determine the practical impact.

LPAD and RPAD add characters on the left or right to reach a requested length. For example, LPAD('42', 5, '0') returns 00042. Padding is helpful for presentation or fixed-width output, but it does not change the underlying column’s stored value unless used in a data-changing statement. If the original value is already longer than the requested width, the result is shortened to fit, so avoid using padding as a substitute for validating input.

Pattern matching with regular-expression functions

When literal search is not enough, Oracle provides regular-expression functions, including REGEXP_LIKE for condition testing, REGEXP_INSTR for finding a match position, REGEXP_SUBSTR for extracting a match, and REGEXP_REPLACE for replacing matches. For example, a condition using REGEXP_LIKE(phone_text, '^[0-9]{3}-[0-9]{4}$') checks for three digits, a hyphen, and four digits across the whole value.

Regular expressions are useful for structured patterns, but they are more complex than literal functions and can be harder to maintain. Oracle’s regular-expression support follows documented POSIX and Unicode-related rules, with details such as match parameters affecting case and newline behavior. A pattern that appears simple should be tested against representative values, including nulls, unexpected punctuation, and non-ASCII text. For a plain replacement such as changing every hyphen to a space, REPLACE is clearer.

Nulls, empty strings, and common pitfalls

Oracle treats a zero-length character string as null in SQL. This surprises users coming from systems that distinguish an empty string from a missing value. As a result, testing a character column with column_name = '' is not a reliable way to find empty values; use IS NULL to test for null or zero-length values under Oracle’s behavior. A string containing one or more spaces is not the same as a zero-length string, so trimming may be needed if whitespace-only entries should count as blank.

Another common mistake is confusing a character set with a substring. As noted above, the second argument to LTRIM and RTRIM is a set of removable characters. To remove a particular multi-character prefix only when it occurs, use a condition and an extraction expression rather than assuming LTRIM removes that prefix as a unit.

Also check the starting position and missing-match behavior of extraction expressions. INSTR returns 0 for no match, while SUBSTR uses one-based positions and treats zero as the first position. Those rules can produce unexpected results when expressions are built from unvalidated data. Add explicit conditions for missing delimiters and test values at the shortest and longest expected boundaries.

Choosing a function and considering performance

Start with the simplest function that expresses the requirement. For a known delimiter, INSTR and SUBSTR are often easier to read than a regular expression. Use REPLACE for a fixed literal, TRIM for edges, and regular-expression functions when the pattern genuinely varies. Clear SQL is easier to review and less likely to mishandle unusual input.

For large tables, consider how string expressions are used in filters. A condition such as UPPER(last_name) = 'SMITH' applies a function to the column; a regular index on last_name may not serve that expression as expected. Oracle supports options such as function-based indexes, but whether one is suitable depends on the workload, data distribution, and query plan. Inspect the execution plan and consult the database’s indexing practices rather than assuming that every string function makes a query slow or that every index will be used.

Finally, distinguish display formatting from data normalization. Removing punctuation or changing case in a query can make a report easier to read, but it may conceal differences that matter for identifiers or names. Preserve original values when the exact input is important, and normalize data only according to a defined business rule.

Frequently Asked Questions

What is the Oracle function for concatenating strings?
Use the || operator, such as 'A' || 'B', or use CONCAT for two operands. Add spaces or punctuation explicitly when they are needed.

How do I get part of a string in Oracle?
Use SUBSTR(string, start, length). Positions start at 1; a negative start counts from the end. Pair it with INSTR when the extraction point depends on a delimiter.

How can I replace text in Oracle SQL?
Use REPLACE(value, search_text, replacement_text) for literal text. Use REGEXP_REPLACE when the target is a pattern, and test the expression on varied inputs.

Does Oracle treat an empty string as NULL?
Yes, Oracle SQL treats a zero-length character string as null. A value containing spaces is still a nonempty value unless those spaces are removed, for example with TRIM.

What is the difference between LENGTH and LENGTHB?
LENGTH measures character length according to Oracle’s character-function rules, while LENGTHB measures bytes. Use the measure that matches the actual field or interface limit.

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.