ANSI SQL String Functions

Updated July 20, 2017

Strings are text fields as opposed to numeric or date fields. SQL provides several methods to manipulate string fields and find specific information about each string. This feature is used to create flexible and powerful SQL statements that return very specific information based on "where" conditions and reformats it once it is returned. Advanced SQL statements can contain several string functions.

Capitalisation Commands

Use "upper(string)" to convert strings to all upper case, "lower(string)" to convert strings to all lower case and "initcap(string)" to convert data to an initial upper case character followed by all lower case characters.

Pad Commands

Add the "lpad(string, length, pad)" function to the "select" statement to pad a string to the left with the "pad" character until it is the specified "length." The "rpad" pads the string to the right.

Trim Commands

Use "ltrim(string, trimlist)" to remove the "trimlist" characters from the leftmost part of the string and "rtrim" to remove characters from the rightmost part of the string.

Replace Commands

Replace all occurrences within the "string" of the "target" with the "replacement" value by utilising the "replace(string, target, replacement)" function. The "translate(string, fromlist, tolist)" function works in a similar manner. Instead of treating the "fromlist" as one value, this command replaces each character in the "fromlist" with the corresponding character in the "tolist".

Number Reformatting

Use the "to_char(number, "format") command to change the format of a number and return it as a string. Format must be in quotes and can include "0", "9", commas, decimals, and dollar signs.

Position Commands

Return a numeric value that represents the length of the string with the "length(string)" function. The "instr(sting, target, start, nth)" fuction returns the numeric position in the string where the "target" is found the "nth" time. If "nth" is not specified, it defaults to the first position. A "0" is returned if the "target" is not found.

Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author

Based in Texas, Sara Wayne has been a freelance writer since 2008, as well as a veteran IT professional. She graduated from Virginia Commonwealth University in 1989 with a Bachelor of Science in management information systems and an English minor. Wayne's work has been published on and