Toad Oracle String Functions: Syntax and Examples in SQL

Formulas & functions · Updated November 22, 2026

Toad for Oracle does not have a separate set of string functions: it sends your SQL to an Oracle database, which provides the functions. In a Toad SQL Editor, use Oracle functions such as SUBSTR, INSTR, REPLACE, TRIM, and REGEXP_SUBSTR in a query; the database version determines which features are available.

What string functions mean in Toad

Toad is a database client and development environment, not a replacement for Oracle’s SQL engine. When you run a query in Toad, Oracle evaluates its expressions and returns the results. That distinction matters when a function is missing or behaves differently than expected: the relevant compatibility question is usually which Oracle database release you are connected to, not which Toad release you installed.

You can use string functions in a query’s SELECT list, in a WHERE condition, or in other SQL expressions where Oracle allows them. For a quick experiment that does not need a table, Oracle’s one-row DUAL table is useful. For example, SELECT UPPER('Toad') FROM dual; returns TOAD. Run such statements in Toad’s SQL Editor using the command for executing a statement; keyboard shortcuts can vary with Toad version and user configuration.

These functions can help standardize names, find delimiters, extract parts of a value, remove unwanted characters, and validate text patterns. They do not change stored data merely because they appear in a SELECT expression. To persist a transformation, use an appropriate data-changing statement, such as UPDATE, and take care to test it first.

Essential Oracle string functions

These built-in functions cover many everyday tasks. The examples are Oracle SQL, so they can be run from Toad when the connected database supports the syntax.

  • UPPER(text) converts letters to uppercase; LOWER(text) converts them to lowercase. INITCAP(text) capitalizes the first letter of each word according to Oracle’s rules.
  • LENGTH(text) returns the number of characters under the applicable character-set semantics. LENGTHB(text) returns the number of bytes, which can differ for multibyte text.
  • SUBSTR(text, start, length) returns part of a string. Positions are normally counted from 1; a negative starting position counts from the end. If the length is omitted, Oracle returns from the starting point through the end.
  • INSTR(text, search_text) returns the position of a matching substring, or zero if it is not found. Optional arguments let you set a starting position and which occurrence to find.
  • TRIM(text) removes leading and trailing spaces by default. LTRIM and RTRIM remove characters from the left or right side.
  • REPLACE(text, search_text, replacement) substitutes matching text. TRANSLATE replaces individual characters according to corresponding character lists, rather than replacing a whole substring.
  • LPAD and RPAD add padding on the left or right to reach a requested length. They are useful for display formatting, but should not be confused with numeric formatting.

For instance, SELECT SUBSTR('DATABASE', 2, 4) FROM dual; returns ATAB. The starting position is 2, and the requested length is four characters. In contrast, SELECT INSTR('TOAD FOR ORACLE', 'OR') FROM dual; returns 10, the position where the first OR begins.

Combining strings and extracting a value

Oracle supports the concatenation operator ||. It joins text expressions without requiring a special Toad feature. For example, SELECT 'Hello, ' || 'Oracle' FROM dual; returns Hello, Oracle. Oracle also provides CONCAT, but it accepts two arguments; use || to join several pieces clearly.

A common practical task is separating a value at a known delimiter. Suppose a table has an email_address column and you want the text before the first at sign. The following expression returns that portion for values containing an at sign:

SUBSTR(email_address, 1, INSTR(email_address, '@') - 1)

This is a useful illustration of nesting: INSTR finds the delimiter, and SUBSTR extracts the characters before it. It is not, by itself, a complete email validation rule. If the delimiter is absent, INSTR returns zero, making the calculated length negative; the result will not provide a meaningful address prefix. Check for the delimiter explicitly in the query or handle such rows separately. Also consider multiple delimiters and the exact data rules before using a quick extraction expression in a production report.

To split more complicated data reliably, first establish its format. A value such as last, first may contain commas inside quoted names, and a simple INSTR/SUBSTR expression will not understand CSV quoting. String functions operate on characters and patterns; they do not automatically parse a structured file format.

Cleaning text and handling whitespace

Use TRIM when unwanted spaces surround a value. For example, SELECT TRIM(' Oracle ') FROM dual; returns Oracle. LTRIM and RTRIM can target one side. Oracle’s trim functions also accept a trim character, but the character argument should be understood as a set of characters to remove from the relevant edge, not as a multi-character phrase to search for anywhere inside the text.

For simple substitutions, REPLACE is often easier to read than a regular expression. SELECT REPLACE('TOAD-TOAD', 'TOAD', 'SQL') FROM dual; returns SQL-SQL. By contrast, TRANSLATE maps individual characters: it is appropriate for substitutions such as converting several specific punctuation marks, but not for replacing a word or phrase as a unit.

A frequent misconception is that trimming spaces removes every kind of invisible whitespace. The default trim behavior targets ordinary space characters at the ends. Tabs, line breaks, nonbreaking spaces, and other characters may need explicit handling, and the exact solution depends on how the data was created and on the database character set. Inspect representative values before applying cleanup to a whole column. A query that makes a value look clean in a report does not repair the underlying stored value unless you deliberately write the result back.

Searching with regular expressions

For pattern-based work, Oracle offers regular-expression functions, including REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_INSTR, and REGEXP_REPLACE. They can recognize patterns that ordinary substring functions cannot conveniently express, such as a sequence of digits or repeated whitespace. They are SQL functions evaluated by Oracle, not a Toad-specific search language.

For example, SELECT REGEXP_SUBSTR('INV-2026-45', '[0-9]+', 1, 2) FROM dual; returns 45. The pattern [0-9]+ matches one or more digits, the third argument starts the search at position 1, and the fourth argument asks for the second match. For a condition rather than an extracted result, REGEXP_LIKE can test whether a value matches a pattern.

Regular expressions are more expressive, but they can be harder to maintain and may cost more to evaluate than a straightforward comparison. If the task is simply to find a fixed delimiter, INSTR is usually clearer. If a pattern must be shared across a team, document what it accepts and rejects, and test edge cases such as empty values, punctuation, and unexpected characters. Oracle’s regular-expression syntax and supported options should be checked against the documentation for the connected database release.

Oracle details that affect results

Oracle treats a zero-length character string as NULL in SQL. This is a notable difference from systems where an empty string is distinct from a null value. A value containing spaces is not the same as a zero-length string, so a blank-looking column may still contain spaces. Functions that receive null inputs can therefore return null or otherwise follow their documented null behavior. Concatenation has special Oracle behavior: concatenating a null value with a non-null string yields the non-null string, while this should not be taken to mean every function handles nulls the same way.

Character counts and byte counts can also differ. In a multibyte character set, one displayed character may occupy multiple bytes, so LENGTH and LENGTHB can return different results. Oracle also provides byte-oriented forms such as SUBSTRB; use them only when byte positions are genuinely what the application requires. For ordinary text manipulation, character-oriented functions are generally the more natural starting point.

Finally, string comparisons and case conversions can be affected by character set and linguistic settings. Do not assume that converting text to uppercase alone solves every language-specific sorting or matching requirement. If a result has to follow a particular locale’s rules, check Oracle’s collation and globalization behavior for the database configuration in use.

Choosing the right function and avoiding problems

Start with the simplest function that matches the data rule. Fixed text and a known separator usually call for INSTR and SUBSTR; trimming edge spaces calls for TRIM; replacing a literal substring calls for REPLACE; and flexible pattern matching may justify a regular expression. Simpler expressions tend to be easier to review, test, and maintain.

  • Test the expression on a small set of representative rows before using it in a report or data change.
  • Include nulls, empty-looking values, missing delimiters, repeated delimiters, and non-ASCII text in the test cases where they are possible.
  • Check whether the function belongs in a filter or projection. Applying a function to a table column in a condition can affect whether an ordinary index is useful; examine the actual query plan rather than assuming performance.
  • When comparing normalized text, make sure the transformation matches the business rule. Uppercasing a value, for example, is not necessarily the right treatment for every language or identifier.
  • Do not concatenate untrusted input into dynamic SQL. Use bind variables for data values; string functions do not make dynamically constructed SQL safe.

If Toad reports an invalid identifier for a string function, confirm the spelling, check that the statement is being sent to Oracle SQL rather than another editor context, and verify the database release and function syntax. If a query runs but returns an unexpected value, reduce it to a small expression against DUAL, then add the real column and conditions back one piece at a time. This separates a syntax issue from unexpected source data.

Frequently Asked Questions

Does Toad have its own string functions?
Not for Oracle SQL. Toad provides the editor and submits statements to Oracle; Oracle’s SQL functions perform the string operations. Other Toad products or database connections may target different engines, whose function sets can differ.

How do I extract text before a character in Toad?
Use SUBSTR with INSTR to find the character and set the substring length. Check that the delimiter exists first, because a missing delimiter makes the expression’s calculated length invalid for the intended extraction.

What is the difference between INSTR and SUBSTR?
INSTR locates text and returns its position; SUBSTR returns text from a specified position and length. They are often combined when the location of a separator determines what to extract.

Why does Oracle return NULL for an empty string?
Oracle SQL treats a zero-length character string as null. A string containing one or more spaces is not empty in that sense, so use an appropriate trim or cleanup step if spaces are the issue.

Should I use REPLACE or REGEXP_REPLACE?
Choose REPLACE for a literal substring. Use REGEXP_REPLACE when the replacement depends on a pattern, such as a variable run of characters. Prefer the simpler function when both can express the required rule.

Can I use these functions in an UPDATE statement?
Yes, Oracle string functions can be used in SQL expressions such as an UPDATE assignment. First run a SELECT that shows the current and proposed values, confirm the affected rows, and follow your organization’s safeguards before changing stored data.

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.