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.
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.
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.
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 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".
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.
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.