ANSI SQL String Functions

Written by sara wayne
  • Share
  • Tweet
  • Share
  • Pin
  • Email
ANSI SQL String Functions
In computer jargon, string means text. (string image by Edsweb from Fotolia.com)

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.

Other People Are Reading

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.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

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