String functions in an Oracle database system can perform many different tasks, and developers often make use of SQL string functions regardless of the purpose of a system. String functions relate to various aspects of text data as well as system requirements, including case, characters, string properties such as length, amendments of strings and sections of strings. Oracle is a powerful database system that provides many useful SQL functions.
Oracle SQL functions can alter the case of one or more characters in a string, or the entire string. The "UPPER" function converts an entire character string to upper case, while the "LOWER" function converts to lower case. The "INITCAP" function converts the initial character of each word in a string to upper case. Additional specialist NLS, or National Language Support, functions provide support for applications using internationalization or localization functionality.
Developers can access characters within strings as well as other character information using Oracle SQL functions. The "CHR" function takes a number as a parameter and returns the character equivalent. The "ASCII" function returns the ASCII value of a given character, provided as a function parameter. The "CONVERT" function allows developers to change the current character set being used. The character set refers to the character encoding system in use on a given system.
Oracle SQL functions can provide access to information about string length and also use this information for further processing. The "LENGTH" function returns the length of a given string, while the "LENGTHB" function returns the length in bytes. The "MAX" function returns the maximum character string within a given data set, while the "MIN" function returns the minimum. String length can also be amended using the "LTRIM" and "RTRIM" functions, which trim set characters from the left and right ends of a string.
SQL functions in Oracle can carry out various alterations on strings. The "REPLACE" function takes the string as a parameter, as well as the string or character to replace, plus what to replace it with, returning the resulting string with amendments made. The "CONCAT" function concatenates two strings passed as parameters, returning a final string comprising both joined together. The "REVERSE" function takes a string and returns it with the characters rearranged in reverse order.
SQL substring functions in Oracle provide the ability to access and alter sections of strings. The "SUBSTR" function takes a string as a parameter, along with the starting position and length of the required substring, which is returned. The "SUBSRTB" performs a similar function, except that the requested substring is returned as its representation in bytes rather than characters. The function "INSTR" checks a given string to determine whether it contains a second string, returning the starting position if it does.
- Syronex: Oracle SQL Built-In Functions
- O'Reilly Online Catalog; SQL in a Nutshell; Kevin Kline with Daniel Kline, Ph.D.; January 2001
- OraDev.com: Oracle String Functions
- Java2s.com: Oracle PL/SQL Tutorial - Character String Functions
- Oracle PL/SQL Code Library and Resources: Oracle/PLSQL String Functions
- Photo Credit Jupiterimages/Photos.com/Getty Images
How to Replace a Substring in Oracle SQL
Oracle allows a portion of a string, called a sub-string, to be easily replaced using structured query language (SQL) functions. The "replace"...