In Oracle-related Java code, string operations usually mean methods on Java’s built-in String class, such as length(), substring(), contains(), and replace(); they are methods, not Oracle-specific functions. Oracle Database also has SQL string functions such as SUBSTR and INSTR, which have different syntax and behavior.
What “Oracle Java string functions” means
The phrase can refer to two related but separate toolsets. Java programs that connect to Oracle Database use the standard Java API, including java.lang.String. Java stored procedures running inside Oracle Database also use Java classes, subject to the Java and database versions supported by that installation. Neither situation turns Java’s string methods into Oracle functions: the methods belong to Java.
By contrast, a SQL statement sent to Oracle Database uses Oracle SQL expressions. For example, Java spells a substring operation text.substring(1, 4), while Oracle SQL uses SUBSTR(text, 2, 3). The difference in start positions is important: Java string indexes start at zero, while Oracle SQL character positions generally start at one. Which tool to use depends on where the operation runs—inside Java code or in a database query.
Common Java String methods
String is part of Java’s standard library and is available without an import because it is in java.lang. These methods cover many everyday tasks:
length()returns the number of UTF-16 code units in the string, not necessarily the number of user-perceived characters.isEmpty()checks whether the length is zero. It does not treat a string of spaces as empty.charAt(index)returns the UTF-16 code unit at a zero-based index. An invalid index causesIndexOutOfBoundsException.substring(beginIndex)returns from the given zero-based index to the end.substring(beginIndex, endIndex)includes the first index and excludes the second.contains(sequence),startsWith(prefix), andendsWith(suffix)test for literal text. These checks are case-sensitive.indexOf(value)finds the first occurrence and returns-1if it is absent. Overloads let you begin searching at a chosen index.replace(oldText, newText)replaces literal character sequences.replaceAll(regex, replacement)interprets its first argument as a regular expression.toLowerCase()andtoUpperCase()change letter case; locale-sensitive text should use the overload that accepts aLocale.equals(other)checks exact content, whileequalsIgnoreCase(other)compares without distinguishing letter case.
These methods return values rather than changing the original string. For example, calling text.toUpperCase() does not modify text; the returned string must be assigned or used directly.
Examples in Java code
Here is a small example using ordinary Java strings. It does not require an Oracle-specific library, so the same operations work in a Java application whether or not it connects to an Oracle database.
String code = " Ora-42 ";
String cleaned = code.trim();
boolean hasPrefix = cleaned.startsWith("Ora");
String upper = cleaned.toUpperCase();
String part = cleaned.substring(0, 3);
System.out.println(cleaned); // Ora-42
System.out.println(hasPrefix); // true
System.out.println(upper); // ORA-42
System.out.println(part); // Ora
In this example, substring(0, 3) returns indexes 0, 1, and 2; the end index 3 is excluded. A common pattern is to check that a string is long enough before taking a substring, or to use a parser designed for the data format rather than assuming every input has the expected shape.
For several pieces of text, String.join can insert a delimiter: String.join(", ", "red", "green", "blue") produces red, green, blue. Java’s split(regex) divides a string using a regular expression, not a literal separator. To split on a period, for example, escape it as text.split("\\.") in Java source. Regular-expression syntax and Java string-literal escaping are two separate layers, which is why these expressions can look heavily escaped.
Java methods versus Oracle SQL functions
Similar names do not guarantee identical results. Oracle SQL includes functions such as SUBSTR, INSTR, LENGTH, REPLACE, LOWER, UPPER, and regular-expression functions including REGEXP_SUBSTR. These are used in SQL expressions; Java methods are called on Java objects. A query might use SUBSTR(customer_name, 1, 3), while Java might use customerName.substring(0, 3).
Choose based on where the work belongs. Filtering or transforming rows as part of a database query may be conveniently expressed in SQL, especially when the database can perform the operation before returning results. Application-specific formatting or processing may fit more naturally in Java. Moving work between the two layers can change behavior because of indexing, character semantics, collation, null handling, and regular-expression rules. Test the actual expression in the environment where it will run.
Null handling is a particularly important difference. Java cannot call an instance method on a null reference; for example, value.length() throws NullPointerException if value is null. Oracle SQL expressions have SQL’s null semantics, and functions commonly return null when given a null argument, though details depend on the function and expression. Check for missing values explicitly rather than assuming the Java and SQL outcomes match.
Indexes, Unicode, and text boundaries
Java’s String uses UTF-16. Consequently, length() counts UTF-16 code units. Many common characters occupy one code unit, but some Unicode characters—such as many emoji—are represented by a surrogate pair and count as two. A combining accent may also be stored as a separate code point after a letter, even though people see the combination as one displayed character. Thus, length() is not a reliable count of visible symbols in every language.
Methods such as charAt and substring use the same UTF-16 indexing model. If an application must count or traverse Unicode code points, Java provides code-point methods such as codePointCount and offsetByCodePoints. If it needs to treat a user-perceived grapheme (for example, an emoji sequence) as one unit, code-point operations alone may still be insufficient; Unicode text segmentation is a separate concern.
Case conversion also deserves care. The no-argument toLowerCase() and toUpperCase() use the default locale, which can lead to different results in different environments for certain letters. For locale-independent identifiers such as protocol keys, Java code often uses toLowerCase(Locale.ROOT) or toUpperCase(Locale.ROOT). For language intended for a particular audience, use the relevant locale or preserve the original text as appropriate.
Immutability, whitespace, and regular expressions
Java strings are immutable: an operation that appears to edit text creates or returns a string value instead of changing the existing object. This simplifies sharing strings but can make repeated concatenation in a large loop inefficient. For building text through many appends, StringBuilder is generally a more suitable mutable buffer; convert it to a string when the result is ready.
Whitespace methods are not interchangeable. trim() removes leading and trailing characters whose code points are at most U+0020; it is not a universal Unicode-whitespace cleaner. strip(), available from Java 11, uses the Java definition of whitespace and is better suited to many Unicode-aware cases. The method isBlank(), also available from Java 11, checks whether a string is empty or consists only of whitespace as defined by Java. Confirm the Java version before using these newer methods, particularly in an Oracle Database environment where the supported Java version depends on the database release and configuration.
Use replace for literal replacement and replaceAll only when a regular expression is intended. In a replacement string for replaceAll, dollar signs and backslashes have special meaning; use Matcher.quoteReplacement when replacement text must be treated literally. Regular expressions can also be harder to read and maintain than a simple literal replacement, so select the least complicated method that meets the requirement.
Using Java strings with Oracle Database
A Java program commonly receives database text through a JDBC driver and then applies Java string methods to the returned value. The database column’s character set, the JDBC mapping, and the Java program’s handling can all matter for international text. If a transformation is performed in SQL, it follows Oracle SQL’s rules; if performed after retrieval, it follows Java’s rules. Applying the same-looking operation in both places is not automatically equivalent.
Oracle Database can run Java code in the database through its Java support, but this is different from using Java in an external application. Available Java versions, deployment requirements, permissions, and supported features depend on the specific Oracle Database release and setup. Consult the documentation for that release before relying on a newer Java method such as strip() or isBlank(). For ordinary Java applications, use the Java version targeted by the project and the JDBC driver compatibility guidance.
Common mistakes to avoid
- Mixing index conventions: Java starts at zero, while Oracle SQL character positions generally start at one. Translate positions deliberately when moving logic between Java and SQL.
- Confusing empty and blank:
isEmpty()is true for"", not for" ". Use a whitespace-aware check when spaces should count as blank. - Comparing strings with
==: In Java,==checks whether references identify the same object. Useequalsfor text content, with a null-safe design where needed. - Expecting replacement to mutate a string: Capture the returned value, such as
text = text.replace("old", "new"). - Using regex accidentally:
replaceAllandsplitaccept regular expressions. Usereplacefor literal replacement and escape regex metacharacters when splitting on them. - Ignoring nulls and locale: Validate possibly null values before calling methods, and choose an explicit locale for locale-independent case normalization.
Frequently Asked Questions
Does Oracle Java have a special string function library?
Not for ordinary string operations. Java uses the standard java.lang.String API. Oracle SQL has its own string functions, and Oracle Database’s Java environment has version-specific support for Java APIs.
What is the Java equivalent of Oracle SQL SUBSTR?
Use substring, remembering that Java indexes from zero and excludes the end index. For a three-character prefix, Java uses text.substring(0, 3); Oracle SQL commonly uses SUBSTR(text, 1, 3).
How do I check whether a Java string contains text?
Call text.contains("part") for a case-sensitive literal search. It returns a boolean. Check that text is not null before calling it.
What is the difference between replace and replaceAll?replace replaces literal character sequences. replaceAll treats its search argument as a regular expression and supports regex-based matching.
How can I remove whitespace from both ends of a string?
Use trim() for its limited traditional behavior, or strip() when running Java 11 or later and Unicode-aware whitespace handling is desired. Neither method removes whitespace from the middle of the string.
Related
- Split by Position in Power Query: A Practical Guide
- TinyTask for Sol’s RNG: What It Can and Can’t Do Safely
- Java split() Method: Split Strings with Regex and Limits
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- ABS Excel Examples: Formulas and UsesIn Excel, use =ABS(number) to return a number’s distance from zero without its negative sign: for example, =ABS(-25)…
- Excel Formula for High and Low Values: MIN and MAXIn Excel, use =MIN(B2:B10) to find the lowest number in a range and =MAX(B2:B10) to find the highest.…
- How to Auto Number Rows in Excel: Formulas and FillTo auto number rows in Excel, enter a row-based formula in the first cell of your numbering column…
- Excel Date Calculation Methods and FormulasIn Excel, calculate dates by adding or subtracting dates and day counts, or use functions such as DATE,…
- COUNTIF in Excel: Formula, Examples, and Common FixesIn Excel, COUNTIF counts cells in a range that meet one condition. For example, with status labels in…
- Excel Protect Sheet: What to Do If You Forgot the PasswordIf you forgot an Excel worksheet password, Microsoft Excel has no built-in way to reveal or reset it:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.