String Functions
This topic describes the string functions supported by StoneDB.
| Function | Description | Example |
|---|---|---|
| ASCII(s) | Returns the numeric value of the leftmost character of string s. | select ASCII('CHINA'); ->67 |
| CHAR_LENGTH(s) CHARACTER_LENGTH(s) | Returns the number of characters in string s. | SELECT CHAR_LENGTH('CHINA'); ->5 |
| CONCAT(s1,s2...sn) CONCAT_WS(x, s1,s2...sn) | Concatenates strings s1, s2, … Sn. | SELECT CONCAT('Welcome to ','CHINA'); ->Welcome to CHINA |
| FIELD(s,s1,s2...) | Returns the index of the first string s in the list of subsequent strings (s1, s2, …). | SELECT FIELD("c","a","b","c","d","e"); ->3 |
| FIND_IN_SET(s1,s2) | Returns the index of the first string s1 within the second string s2. | SELECT FIND_IN_SET("c","a,b,c,d,e"); ->3 |
| FORMAT(x,n) | Returns a number formatted to n decimal places. x specifies the number to format. The return value is in the #,###.## format. | SELECT FORMAT(9105885500.534,2); ->9,105,885,500.53 |
| INSERT(s1,x,len,s2) | Inserts substring s2 at a given position up to the specified number of characters in string s1. x specifies position to insert. len specifies the number of characters. | SELECT INSERT('Welcome to CHINA',1,6,'I love'); ->I lovee to CHINA |
| LOCATE(s1,s) | Returns the position of the first occurrence of substring s1 in string s. | SELECT LOCATE('db','stonedb'); ->6 |
| LCASE(s) LOWER(s) | Returns every character in string s in lowercase. | SELECT LCASE('STONEDB'); ->stonedb |
| LEFT(s,n) | Returns the leftmost n characters in string_ s_. | SELECT LEFT('stonedb',5); ->stone |
| LPAD(s1,len,s2) | Left-pads string s2 with string s1 to the specified length len. | SELECT LPAD('one',5,'st'); ->stone |
| LTRIM(s) | Remove leading spaces in string s. | SELECT LTRIM(' STONEDB'); ->STONEDB |
| MID(s,n,len) | Returns a substring of string s starting from a given position. n specifies the position. len specifies the length of the substring. This function is the Synonym for SUBSTRING(s,n,len). | SELECT MID('stonedb',2,3); ->ton |
| POSITION(s1 IN s) | Returns the position of the first occurrence of substring s1 in string s. | SELECT POSITION('db'in'stonedb'); ->6 |
| REPEAT(s,n) | Repeats string s for n times. | SELECT REPEAT('hello',3); ->hellohellohello |
| REPLACE(s,s1,s2) | Replaces substring s1 in string s with substring s2. | SELECT REPLACE('stonedb','db','x'); ->stonex |
| REVERSE(s) | Reverses the characters in string s. | SELECT REVERSE('stonedb'); ->bdenots |
| RIGHT(s,n) | Returns n rightmost characters in string s. | SELECT RIGHT('stonedb',5); ->onedb |
| RPAD(s1,len,s2) | Right-pads string s2 to string s1 to the specified length len. | SELECT RPAD('stone',7,'db'); ->stonedb |
| RTRIM(s) | Remove trailing spaces in string s. | SELECT RTRIM('STONEDB '); ->STONEDB |
| SPACE(n) | Returns n spaces. | SELECT SPACE(10); -> |
| STRCMP(s1,s2) | Compares the lengths of strings s1 and_ s2_. - If s1 = s2, 0 is returned. - If s1 > s2, 1 is returned. - If s1 < s2, -1 is returned. | SELECT STRCMP('stonedb','stone'); ->1 |
| SUBSTR(s, start, length) | Returns a substring of the specified length within string s. start specifies the position from which the substring starts. | SELECT SUBSTR('STONEDB',2,3); ->TON |
| SUBSTRING_INDEX(s, delimiter, number) | Returns a substring from string s before the specified number of occurrences of the delimiter. | SELECT SUBSTRING_INDEX('stonedb','n',1); ->sto |
| TRIM(s) | Removes leading and trailing spaces in string s. | SELECT TRIM(' STONEDB '); ->STONEDB |
| UCASE(s) UPPER(s) | Returns every character in string s in uppercase. | SELECT UCASE('stonedb'); ->STONEDB |