ANSI SQL String Functions

Written by sara wayne | 13/05/2017
ANSI SQL String Functions
In computer jargon, string means text. (string image by Edsweb from

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.

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.