String functions allow you to manipulate string (character) data with functionality such as concatenating strings together or finding substrings within strings.
| Function | Usage and description |
| Length |
Usage: LENGTH(str) OR CHAR_LENGTH(str) OR LEN(STR) Returns the length of the string str measured in number of characters (even for multi-byte characters). |
| Find Substring |
Usage: CHARINDEX(substr,str), STRPOS(str,substr) Returns the position of the first occurrence of substring substr in string str. Note that the functions CHARINDEX and STRPOS are synonomous, except that the parameter order is reversed. Returns 0 if substr is not in str. |
| CHR |
Usage: CHR(number) Returns the character that matches the ASCII code of the provided number. |
| Concatenate |
Usage: CONCAT(str1,str2) or str1 || str2 || str3 || ... Returns the string that results from concatenating the arguments. Note that the CONCAT function accepts only two arguments. Use the concatenation operator || to concatenate any number of strings. |
| Capitalize |
Usage: INITCAP(string) This function will capitalize the first letter of each word in the input string and return the result. |
| Left |
Usage: LEFT(str,len) Returns the leftmost len characters from the string str, or NULL if any argument is NULL. |
| Right |
Usage: RIGHT(str,len) Returns the rightmost len characters from the string str, or NULL if any argument is NULL. |
| Lower |
Usage: LOWER(str) Returns the string str with all characters changed to lowercase. |
| Upper |
Usage: UPPER(str) Returns the string str with all characters changed to uppercase. |
| Left Pad |
Usage: LPAD(str,len,padstr) Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters. |
| Right Pad |
Usage: RPAD(str,len,padstr) Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters. |
| Left Trim |
Usage: LTRIM(str, 'trim characters') Returns the string str with specified leading characters removed. If trim chacters are not provided, whitespaced is removed. |
| Right Trim |
Usage: RTRIM(str, 'trim characters') Returns the string str with specified trailing characters removed. If trim chacters are not provided, whitespaced is removed. |
| Trim |
Usage: TRIM(str), TRIM({BOTH} ['characters' FROM] str) Returns the string str with all leading and trailing 'characters' removed. If no characters are specified, whitespace is removed. |
| Quoting |
Usage: QUOTE_LITERAL(str) Adds single quotes around the provided string. |
| Repeat |
Usage: REPEAT(str,count) Returns a string consisting of the string str repeated count times. If count is less than 1, returns an empty string. Returns NULL if str or count are NULL. |
| Replace |
Usage: REPLACE(str,from_str,to_str) Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str. |
| Reverse |
Usage: REVERSE(str) Returns the string str with the order of the characters reversed. |
| Extract Parts of a String |
Usage: SPLIT_PART(string,delimiter,part number) This function uses the supplied delimiter to split the provided string into parts, and returns the part specified by part number (which must be an integer greater than 0). If the provided part number is larger than the number of parts the string is split into, the function returns an empty string. |
| Substring |
Usage: SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len) Returns a substring len characters long from string str, starting at position pos. |