Oracle SQL Developer does not have a separate set of string functions: it is the database tool where you write and run Oracle SQL and PL/SQL. To work with text, use Oracle Database functions such as SUBSTR, INSTR, REPLACE, TRIM, and REGEXP_REPLACE in a SQL Developer worksheet.
What string functions do in Oracle
String functions inspect, extract, combine, change, and format character data. They are useful when cleaning imported values, finding a delimiter, preparing display labels, or comparing text in a query. You can call them in a SELECT statement, in many other SQL expressions, and in PL/SQL, subject to the rules of the statement in which they appear.
SQL Developer is Oracle’s desktop development environment; the database performs the function. That distinction matters: the available behavior depends primarily on the Oracle Database version, character set, and SQL semantics, not on a special SQL Developer function library. In SQL Developer, open a SQL worksheet connected to a database and run a query such as:
SELECT UPPER('hello') AS result
FROM dual;
The query returns HELLO. Oracle’s DUAL table is a one-row table commonly used to evaluate expressions without selecting from a business table. For real data, replace dual with a table name and use the relevant column in place of the literal.
Combining strings
The most familiar way to join text in Oracle SQL is the concatenation operator, two vertical bars: ||. For example, if a table has first_name and last_name columns, this expression creates a display name:
SELECT first_name || ' ' || last_name AS full_name
FROM employees;
Oracle also provides CONCAT, but it accepts two arguments. To join three pieces, nest calls; in everyday SQL, the || operator is often easier to read. Oracle treats a zero-length character string as NULL in SQL, and concatenation has special null behavior: concatenating a null value with a non-null string yields the non-null string. That can make a missing name component appear to vanish rather than making the whole result null. If the output must show a substitute, handle it explicitly with NVL or COALESCE, for example NVL(middle_name, '')—while remembering Oracle’s empty-string rule. A visible substitute such as NVL(middle_name, 'Unknown') avoids that ambiguity.
Extracting and locating text
SUBSTR extracts part of a string, while INSTR finds the position of a character or substring. Oracle character positions start at 1, not 0. A negative starting position for SUBSTR counts from the end; a start position of 0 is treated as 1. If you omit the length argument, SUBSTR returns from the starting position through the end.
SELECT SUBSTR('INV-2026-041', 5, 4) AS year_part,
INSTR('INV-2026-041', '-') AS first_dash,
INSTR('INV-2026-041', '-', 1, 2) AS second_dash
FROM dual;
The results are 2026, 4, and 9. The fourth argument to INSTR chooses which occurrence to find, so the final expression finds the second dash. These functions are handy when data follows a consistent pattern. If the input can vary in structure, first check whether the delimiter exists, or consider a regular expression or a more robust parsing approach.
For example, to return the part of a value before the first dash, locate the dash and use that position to set the substring length. If no dash exists, INSTR returns 0; feeding that result into a substring expression may not give the intended output. A CASE expression can make the missing-delimiter case explicit. For a simple task, a direct, readable expression is often preferable to a complicated one-liner.
Changing case and replacing characters
Use UPPER, LOWER, and INITCAP to change letter case. UPPER('oracle database') returns ORACLE DATABASE; LOWER returns lowercase text; and INITCAP capitalizes the first letter of each word according to its rules. These functions are useful for presentation and for normalizing text in comparisons, but case conversion is not the same thing as correcting spelling or applying every language’s capitalization conventions.
REPLACE substitutes every occurrence of a search string with replacement text. For example, REPLACE('555.010.2200', '.', '-') produces 555-010-2200. If the replacement argument is omitted, Oracle removes matching occurrences. TRANSLATE works differently: it maps individual characters by position in two character lists. For instance, TRANSLATE('Oracle', 'aeiou', '12345') produces Or1cl2, replacing lowercase vowels one character at a time. Choose REPLACE for a whole substring and TRANSLATE for character-by-character mapping.
Trimming and padding text
TRIM removes leading characters, trailing characters, or both. With no direction specified, it removes spaces from both ends. LTRIM and RTRIM focus on the left and right ends. For example, TRIM(' ready ') returns ready. Trimming is commonly applied to imported values where unwanted spaces affect joins, filters, or reports.
Do not assume TRIM removes every kind of whitespace or every invisible character. Tabs and other characters may need separate handling, and what counts as a blank can depend on the actual input. Inspect the source value if a comparison still fails after trimming.
LPAD and RPAD add characters on the left or right until a requested display length is reached. For example, LPAD('42', 5, '0') returns 00042. Padding is useful for display or fixed-width formats, but it does not change the underlying value into a number or guarantee that the result is suitable for numeric sorting. Also, if the source is already longer than the requested length, padding functions can return a shortened result. Choose the requested length with that behavior in mind.
Regular-expression string functions
For patterns more complex than a fixed substring, Oracle offers regular-expression functions. REGEXP_SUBSTR returns text matching a pattern, REGEXP_INSTR finds a matching position, and REGEXP_REPLACE replaces matching text. REGEXP_LIKE is a condition used to test whether text matches a pattern, commonly in a WHERE clause.
SELECT REGEXP_SUBSTR('Order A-2048', '[0-9]+') AS digits,
REGEXP_REPLACE('555.010.2200', '[^0-9]', '') AS digits_only
FROM dual;
The first expression returns 2048; the second returns 5550102200. In the pattern, [0-9]+ means one or more digits, while [^0-9] matches a character that is not a digit. Regular expressions can be powerful for variable formats, but they are harder to maintain than ordinary functions and may require more processing. Prefer INSTR or REPLACE when the task is simple and the data format is predictable.
Character length, bytes, and international text
LENGTH reports a string’s length in characters according to Oracle’s character semantics, while LENGTHB reports the length in bytes. A character may take more than one byte in a multibyte database character set, so the two values need not match. This distinction matters when checking storage limits, designing fixed-width exports, or investigating differences between systems.
Oracle also has length and substring variants with different character or byte semantics, including LENGTHC and SUBSTRB. Use these only when you know which unit the requirement specifies. A limit of 10 characters is not necessarily a limit of 10 bytes. For multilingual data, test with representative characters from the actual data rather than assuming every character occupies one byte.
NULLs, matching, and common mistakes
Many string functions return NULL when their input is null, so a blank-looking output can result from a null source rather than from a failed transformation. Oracle SQL treats a zero-length character string as null, another point that differs from some programming languages and database systems. Use IS NULL to test for null; comparing a value with = NULL does not work as an ordinary equality test.
- Check whether positions are 1-based before using a value from another language or tool.
- Confirm that the search text actually occurs;
INSTRreturns 0 when it does not. - Distinguish characters from bytes when measuring multilingual text or matching a storage limit.
- Be deliberate about case sensitivity. Converting both sides with
UPPERcan help with simple comparisons, but collation and linguistic requirements may call for other settings. - Avoid applying a function to an indexed column in a filter without considering performance. A predicate such as
UPPER(customer_name) = 'LEE'may not use a normal index oncustomer_namein the same way as a direct comparison; an appropriate function-based index or a different data design may be relevant.
For performance-sensitive queries, check the execution plan and test with representative data. A function that is convenient in a small example can add work when applied to every row in a large table. When you repeatedly need a normalized form, storing clean data or using a suitable index may be better than recalculating it for every query.
Choosing the right function
Start with the simplest function that precisely describes the operation. For joining values, use ||; for a known position, use SUBSTR; for locating fixed text, use INSTR; for replacing a literal substring, use REPLACE; and for spaces at the ends, use TRIM. Reach for regular expressions when the rule is genuinely a pattern, such as extracting a run of digits from text with inconsistent separators.
Before putting an expression into a production query, try it against ordinary values and edge cases: nulls, empty strings, missing delimiters, leading or trailing spaces, punctuation, and non-English characters where relevant. Give the computed column a useful alias so results in SQL Developer’s query grid are easy to interpret. If a transformation is used repeatedly across an application, document the rule so different queries do not silently clean the same data in different ways.
Frequently Asked Questions
Does SQL Developer have its own string functions?
Not for Oracle SQL queries. SQL Developer is the client used to write and run statements; Oracle Database supplies the SQL functions. The behavior depends on the connected database and its settings.
How do I join two strings in Oracle SQL?
Use the || operator, such as first_name || ' ' || last_name. Oracle also has CONCAT, which takes two arguments, but the operator is convenient for joining several pieces.
How do I get text between two characters?
For a predictable format, combine INSTR to find delimiter positions with SUBSTR to extract the text. Check for missing delimiters before relying on the positions, because a missing match makes INSTR return 0.
What is the difference between SUBSTR and INSTR?SUBSTR returns part of a string based on a starting position and optional length. INSTR returns the position where a specified character or substring occurs.
Why does LENGTH differ from LENGTHB?LENGTH measures characters, while LENGTHB measures bytes. In a multibyte character set, some characters use more than one byte, so the results can differ.
How can I remove punctuation from a value?
For a simple known character, use REPLACE. For a broader rule, use REGEXP_REPLACE, such as replacing characters outside the digits range with an empty string. Check the resulting data carefully before using it as an identifier or numeric value.
Related
- Balance C/D in Accounting: Meaning and Examples
- A/B Split Testing: Meaning, Method, and Common Mistakes
- Balance of Payments Theory: Meaning and How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel DGET vs INDEX MATCH: Which Should You Use?For a normal lookup by one key, INDEX MATCH is usually the straightforward choice; DGET is useful when…
- Does Excel Have a ROUNDUP Function? Here’s How It WorksYes—Excel has a ROUNDUP function, and it rounds a number away from zero to the number of decimal…
- How to Create Rules in Outlook to Sort Email AutomaticallyTo create a rule in Outlook, open Settings > Mail > Rules in new Outlook or Outlook on…
- Concatenate Text Cells in Excel: Formulas and TipsTo concatenate text cells in Excel, join their references with the ampersand operator, such as =A2&" "&B2 to…
- Excel Months Left in Year: Formula & GuideTo calculate the months left in the year from a date in Excel, use the formula =DATEDIF(TODAY(),DATE(YEAR(TODAY()),12,31),"m") —…
- Calculate Percentage in Excel: Formulas and Table TipsTo calculate a percentage in Excel, divide the part by the total, then format the result as a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.