Oracle database string functions let you measure, extract, search, compare, format, and change text in SQL. Common choices include LENGTH, SUBSTR, INSTR, TRIM, REPLACE, and the regular-expression functions; the right one depends on whether you need literal text handling, pattern matching, or character-set-aware behavior.
What Oracle string functions do
Oracle provides built-in SQL functions for working with character values, including CHAR, VARCHAR2, NCHAR, NVARCHAR2, and, with some restrictions, large character objects such as CLOB and NCLOB. A function takes an expression as input and returns a value or, for some conditions, a true-or-false result. Functions can be used in a query’s select list, filters, joins, grouping, and ordering.
Most examples below use DUAL, Oracle’s one-row table for evaluating an expression without querying application data. For example, SELECT LENGTH('Oracle') FROM dual; returns 6. In a real query, replace the literal with a column or expression, such as LENGTH(customer_name).
String operations are not interchangeable. INSTR finds a position, while SUBSTR returns text at a position. REPLACE substitutes literal text, while REGEXP_REPLACE interprets a pattern. Choosing the simpler function when it fits usually makes a query easier to read.
Measure and combine text
LENGTH returns the length of a character value, normally measured in characters according to the input character set. For example, SELECT LENGTH('Database') FROM dual; returns 8. Oracle also has LENGTHB, which measures bytes, and LENGTHC, LENGTH2, and LENGTH4, which count according to Unicode complete-character, UCS2, and UCS4 conventions. These can differ for multibyte or supplementary characters, so do not assume that character count and storage bytes are identical.
To join values, use the concatenation operator || or the CONCAT function. The operator can combine several parts in one expression: SELECT 'Order ' || order_id FROM orders;. CONCAT accepts two arguments, for example CONCAT('Ora', 'cle'), so the operator is often more convenient when joining more than two expressions. If a value is not character data, Oracle may convert it implicitly; for predictable output, explicitly convert dates or numbers with an appropriate format model using TO_CHAR.
Extract text and find positions
SUBSTR extracts part of a string. Its usual form is SUBSTR(string, start_position, length), with the length optional. Positions begin at 1; a position of 0 is treated as 1, and a negative position counts backward from the end. A requested length shorter than 1 returns NULL.
SELECT SUBSTR('ORACLE', 2, 3) FROM dual;returnsRAC.SELECT SUBSTR('ORACLE', -3, 2) FROM dual;returnsAC.
Related variants include SUBSTRB, which works with byte positions, and SUBSTRC, SUBSTR2, and SUBSTR4, which use Unicode-oriented units. Use a byte variant only when byte-level boundaries are genuinely what the task requires; cutting a multibyte character by bytes can produce an invalid or unusable fragment.
INSTR returns the position of a substring, or zero when it is not found. For example, SELECT INSTR('red,blue,green', ',', 1, 2) FROM dual; returns 9, the position of the second comma. Its optional arguments specify the starting position and which occurrence to find. A negative starting position searches from the end toward the beginning. The result is still a position measured from the start of the string. INSTRB and the corresponding character-set variants provide other measurement conventions.
Change case, trim, and pad
For basic case conversion, use UPPER, LOWER, or INITCAP. These return uppercase, lowercase, or title-style text. For instance, SELECT LOWER('Hello WORLD') FROM dual; returns hello world. Case conversion is useful for display or for a simple case-insensitive comparison, but language-specific case rules and database collation can affect comparison behavior. Do not treat uppercasing as a universal substitute for a carefully chosen linguistic comparison.
TRIM removes leading, trailing, or both-sided occurrences of a specified trim character; if none is specified, it removes ordinary spaces. For example, TRIM(BOTH '-' FROM '--ready--') returns ready. The trim character is a single character, not a multi-character phrase. LTRIM and RTRIM are alternatives for removing characters from the left or right. Their second argument is a set of characters to remove, not a whole prefix or suffix: LTRIM('xyOracle', 'xy') removes leading occurrences of either x or y.
LPAD and RPAD extend a value to a requested length by adding padding on the left or right. For example, LPAD('42', 5, '0') produces 00042. Padding is useful for presentation, such as fixed-width labels, but it does not change the stored value unless the result is written back. If the requested length is shorter than the input, the result is shortened, so check the target length when truncation would be undesirable.
Replace literal text or translate characters
Use REPLACE for literal substitution. It replaces every occurrence of a search string in the source; if the replacement argument is omitted, matching text is removed. For example, SELECT REPLACE('A-12-34', '-', '/') FROM dual; returns A/12/34. This is often clearer and less error-prone than a regular expression when the target is a fixed string.
TRANSLATE maps individual characters rather than whole sequences. It pairs characters in its second argument with characters in its third argument, position by position. For example, TRANSLATE('2,340.50', ',.', '.,') swaps commas and periods, returning 2.340,50. Characters in the source mapping that have no corresponding character in the target mapping are removed from the input. That behavior can be useful for stripping a known set of characters, but it is not equivalent to replacing a multi-character word.
Neither function should be used casually to convert formatted numbers or dates. The punctuation may have meaning defined by a locale or format model. For data conversion, use explicit numeric or date conversion functions and format models rather than assuming that punctuation replacement produces a valid value.
Use regular expressions for patterns
Oracle’s regular-expression functions handle pattern-based tasks. REGEXP_LIKE is a condition used in a query predicate, while REGEXP_SUBSTR extracts a matching substring, REGEXP_INSTR reports a match position, and REGEXP_REPLACE substitutes matches. For example, SELECT REGEXP_REPLACE('Room 204', '[0-9]', 'X') FROM dual; returns Room XXX, replacing each digit that matches the pattern.
In a filter, a pattern can identify values that contain only digits: WHERE REGEXP_LIKE(code, '^[0-9]+$'). The anchors mean the pattern must match the whole value, and the plus sign requires one or more digits. Without anchors, a condition may match a value that merely contains a digit sequence. Regular expressions are powerful, but their pattern syntax can be harder to maintain and may require more work than simple literal functions. For fixed delimiters or exact substrings, consider INSTR, SUBSTR, or REPLACE first.
Understand NULL and empty strings
Oracle currently treats a zero-length character string such as '' as NULL. This differs from systems that distinguish an empty string from a missing value, and it matters when testing or transforming text. A comparison such as column = '' does not test for a distinct empty value; use column IS NULL when the intention is to find nulls. Concatenation is a notable special case: concatenating a null value with a non-null character value yields the non-null value, so 'A' || NULL produces A.
Most string functions return NULL when their relevant input is null, but behavior should be checked for the particular function and expression. To supply a fallback, NVL or COALESCE can be useful, though they are null-handling functions rather than string-manipulation functions. For example, COALESCE(TRIM(phone), 'Not supplied') returns a label when the trimmed value is null. Because trimming spaces from a value containing only spaces can result in an empty string, Oracle’s empty-string rule means that result is treated as null.
Character sets and data-type details
A visible character is not always one byte, and some displayed symbols may be represented by more than one code unit. This is why Oracle offers byte-based and Unicode-oriented variants such as LENGTHB and SUBSTR2. Use regular character functions for ordinary text work unless an application requirement specifically defines limits in bytes or a particular Unicode unit. When a column contains multilingual text, test the relevant functions with representative characters from its actual data.
Data type also matters. CHAR values are fixed-width and can be space-padded, whereas VARCHAR2 values are variable-width. Comparisons and trailing spaces can therefore behave differently depending on types and context. Large-object values have additional restrictions: not every character function or variant accepts every LOB type, and operations can have different performance implications. Check the Oracle SQL Language Reference for the database release in use when applying a function to a CLOB or NCLOB.
Performance and indexing considerations
Applying a function to a column in a filter can affect whether Oracle can use an ordinary index on that column efficiently. For example, WHERE UPPER(last_name) = 'SMITH' transforms each candidate value for comparison; an index built only on last_name may not serve that expression as directly as an index designed for it. Oracle supports function-based indexes, which can index expressions such as UPPER(last_name), when that access pattern is appropriate. An index has storage and maintenance costs, so consider the query workload and execution plan rather than adding one automatically.
When possible, compare consistently normalized values or use a suitable collation and data design. For pattern searches, a leading wildcard such as LIKE '%son' generally limits the usefulness of a conventional index compared with a prefix search such as LIKE 'son%'; actual plans depend on the schema, optimizer, and query. Check execution plans and test with realistic data before drawing conclusions about performance.
How to choose the right function
Start with the simplest operation that matches the requirement. The following distinctions prevent common mistakes:
- Measure text with
LENGTH; useLENGTHBonly when bytes are the intended unit. - Extract by position with
SUBSTR, and find a literal occurrence withINSTR. - Remove edge characters with
TRIM,LTRIM, orRTRIM; do not mistake their character-set argument for a whole phrase. - Replace a fixed substring with
REPLACE; map individual characters withTRANSLATE. - Use regular-expression functions when the target is genuinely a pattern, such as a variable sequence of digits or whitespace.
Also decide whether the task is about presentation or data semantics. Padding a code for display is different from changing the stored identifier. Converting a number to text is different from replacing punctuation in text that happens to look numeric. Keeping those distinctions clear makes SQL more predictable and easier for another person to maintain.
Frequently Asked Questions
What is the difference between SUBSTR and INSTR? SUBSTR returns a portion of a string based on a starting position and optional length. INSTR searches for a substring and returns its position, or zero if it is absent.
How do I remove spaces from a string in Oracle? Use TRIM(value) to remove ordinary spaces from both ends, or LTRIM(value) and RTRIM(value) for one side. These functions do not remove spaces inside the text.
How can I replace every occurrence of text? Use REPLACE(source, search_string, replacement_string). It replaces all occurrences of the literal search string. Use REGEXP_REPLACE when the target is a pattern rather than fixed text.
Does Oracle treat an empty string as NULL? Yes. In current Oracle SQL, a zero-length character string is treated as null. Consequently, IS NULL is the appropriate test; an empty string is not a separate character value.
What is the difference between LENGTH and LENGTHB? LENGTH counts characters according to the character-set rules for the value, while LENGTHB counts bytes. For multibyte text, the results may differ, so use the unit that matches the requirement.
Related
- Split in SQL: Practical Methods for Common Databases
- T-SQL STRING_SPLIT: Syntax, Order, and Examples
- JavaScript String Split: Syntax, Examples, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Column Addition Formula: Sum a ColumnTo add every number in an Excel column, use =SUM(B:B), replacing B with the column you want to…
- Calculate Percentage in Excel: Formulas for a ColumnTo calculate a percentage in an Excel column, divide each part by its whole, then format the results…
- Balance of Payments Equation: Formula and Key TermsThe balance of payments equation records a country’s transactions with the rest of the world: current account +…
- Excel Formula Bot Free? Free Plans, Limits, and Safe UseYes—many Excel formula bots offer free access, but it is usually limited by usage, features, or account requirements.…
- Find the Highest Value in Excel with the MAX FunctionTo find the highest number in an Excel range, enter =MAX(B2:B20) in an empty cell, replacing B2:B20 with…
- Excel Probability Analysis: Methods, Formulas, and ExamplesExcel probability analysis means estimating or calculating the chance of an event from observed data, a probability model,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.