📖 MySQL String and Numeric Functions
I have listed all of the string functions from MySQL - more than in the book. You don't have to learn or use them all, but it's always nice to know what options are available.
| Name with Link to MySQL Dev Docs | Description |
|---|---|
| CHAR_LENGTH(str) | Return number of characters in argument |
| CHAR() | Return the character for each integer passed. Common control characters are: CHAR(9) - tab CHAR(13,10) - Carriage Return, Line Feed |
| CONCAT_WS(sep, str,str,...) | Return concatenate with separator |
| CONCAT() | Return concatenated string |
| FORMAT(str,ndec) | Return a number formatted to specified number of decimal places |
| INSERT(str,pos,len,repl) | Insert a substring at the specified position up to the specified number of characters |
| LEFT(str,len) | Return the leftmost number of characters as specified |
| LENGTH(str) | Return the length of a string in bytes |
| LIKE | Simple pattern matching |
| LOCATE(substr,str) | Return the position of the first occurrence of substring |
| LOWER(str) | Return the argument in lowercase |
| LPAD(str,len,padstr) | Return the string argument, left-padded with the specified string |
| LTRIM(str) | Remove leading spaces |
| NOT LIKE | Negation of simple pattern matching |
| QUOTE(str) | Escape the argument for use in an SQL statement |
| REGEXP | Pattern matching using regular expressions |
| REPEAT(str,cnt) | Repeat a string the specified number of times |
| REPLACE(str,from_str,to_str) | Replace occurrences of a specified string |
| REVERSE(str) | Reverse the characters in a string |
| RIGHT(str,len) | Return the specified rightmost number of characters |
| RPAD(str,len,padstr) | Append string the specified number of times |
| RTRIM(str) | Remove trailing spaces |
| SPACE(n) | Return a string of the specified number of spaces |
| SUBSTR(str,pos,len) | Return the substring starting at pos for len characters. len is optional. If omitted, goes to end of string, |
| SUBSTRING_INDEX(str,delim,cnt) | Return a substring from a string before the specified number of occurrences of the delimiter |
| SUBSTRING(str,pos,len) | Return the substring starting at pos for len characters. len is optional. If omitted, goes to end of string, |
| TRIM(str) | Remove leading and trailing spaces |
| UPPER(str) | Convert to uppercase |
Numeric functions are used to calculate mathematical and statistical information in queries.
| Name with Link to MySQL Dev Docs | Description |
|---|---|
| ABS() | Return the absolute value |
| CEIL() | Return the smallest integer value not less than the argument |
| CEILING() | Return the smallest integer value not less than the argument |
| FLOOR() | Return the largest integer value not greater than the argument |
| PI() | Return the value of pi |
| POWER() | Return the argument raised to the specified power |
| RAND() | Return a random floating-point value |
| ROUND() | Round the argument |
| SIGN() | Return the sign of the argument |
| SIN() | Return the sine of the argument |
| TRUNCATE() | Truncate to specified number of decimal places |