String Functions Flashcards
(31 cards)
ASCII(str)
Returns the numeric value of the leftmost character of the string str.
Returns 0 if str is an empty string. Returns NULL if str is NULL. ASCII() works for characters with numeric values from 0 to 255.
testdb=# SELECT ASCII(‘2’);<br></br>
+———————————————————+<br></br>
| ASCII(‘2’) |<br></br>
+———————————————————+<br></br>
| 50 |<br></br>
+———————————————————+<br></br>
1 row in set (0.00 sec)<br></br>
<br></br>
testdb=# SELECT ASCII(‘dx’);<br></br>
+———————————————————+<br></br>
| ASCII(‘dx’) |<br></br>
+———————————————————+<br></br>
| 100 |<br></br>
+———————————————————+<br></br>
1 row in set (0.00 sec)
BIT_LENGTH(str)
Returns the length of the string str in bits.
testdb=# SELECT BIT_LENGTH(‘text’);
+———————————————————+
| BIT_LENGTH(‘text’) |
+———————————————————+
| 32 |
+———————————————————+
1 row in set (0.00 sec)
CHAR_LENGTH(str)
Returns the length of the string str, measured in characters. A multi-byte character counts as a single character. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.
testdb=# SELECT CHAR_LENGTH(‘text’);
+———————————————————+
| CHAR_LENGTH(‘text’) |
+———————————————————+
| 4 |
+———————————————————+
1 row in set (0.00 sec)
CHARACTER_LENGTH(str)
CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().
CONCAT(str1,str2,…)
Returns the string that results from concatenating the arguments. It may have one or more arguments. If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast, as in this example −
testdb=# SELECT CONCAT(‘My’, ‘S’, ‘QL’);
+———————————————————+
| CONCAT(‘My’, ‘S’, ‘QL’) |
+———————————————————+
| MySQL |
+———————————————————+
1 row in set (0.00 sec)
CONCAT_WS(separator,str1,str2,…)
CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.
testdb=# SELECT CONCAT_WS(‘,’,’First name’,’Last Name’ );
+———————————————————+
| CONCAT_WS(‘,’,’First name’,’Last Name’ ) |
+———————————————————+
| First name, Last Name |
+———————————————————+
1 row in set (0.00 sec)
LCASE(str)
LCASE() is a synonym for LOWER().
LEFT(str,len)
Returns the leftmost len characters from the string str, or NULL if any argument is NULL.
testdb=# SELECT LEFT(‘foobarbar’, 5);
+———————————————————+
| LEFT(‘foobarbar’, 5) |
+———————————————————+
| fooba |
+———————————————————+
1 row in set (0.00 sec)
LENGTH(str)
Returns the length of the string str, measured in bytes. A multi-byte character counts as multiple bytes. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.
testdb=# SELECT LENGTH(‘text’);
+———————————————————+
| LENGTH(‘text’) |
+———————————————————+
| 4 |
+———————————————————+
1 row in set (0.00 sec)
LOWER(str)
Returns the string str with all characters changed to lowercase according to the current character set mapping.
testdb=# SELECT LOWER(‘QUADRATICALLY’);
+———————————————————+
| LOWER(‘QUADRATICALLY’) |
+———————————————————+
| quadratically |
+———————————————————+
1 row in set (0.00 sec)
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.
testdb=# SELECT LPAD(‘hi’,4,’??’);
+———————————————————+
| LPAD(‘hi’,4,’??’) |
+———————————————————+
| ??hi |
+———————————————————+
1 row in set (0.00 sec)
LTRIM(str)
Returns the string str with leading space characters removed.
testdb=# SELECT LTRIM(‘ barbar’);
+———————————————————+
| LTRIM(‘ barbar’) |
+———————————————————+
| barbar |
+———————————————————+
1 row in set (0.00 sec)
MID(str,pos,len)
MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).
POSITION(substr IN str)
POSITION(substr IN str) is a synonym for LOCATE(substr,str).
QUOTE_IDENT(string text)
Returns the given string suitably quoted to be used as an identifier in an SQL statement string. In the function QUOTE_IDENT, Quotes are added only if necessary.
The following are the examples for all these functions
testdb=# SELECT QUOTE_IDENT(‘Foo bar’);
quote_ident
————-
“Foo bar”
(1 row)
REGEXP_MATCHES()
REGEXP_MATCHES(string text, pattern text [, flags text]) function performs a pattern match of expr against pattern. Returns 1 if expr matches pat; otherwise it returns 0.
If either expr or pat is NULL, the result is NULL.
REGEXP_MATCHES is not case sensitive, except when used with binary strings.
testdb=# SELECT REGEXP_MATCHES(‘ABCDEF’,’A%C%%’);
regexp_matches
—————-
(0 rows)
REGEXP_REPLACE()
REGEXP_REPLACE(string text, pattern text, replacement text [, flags text]) function replaces substring(s) matching a POSIX regular expression.
testdb=# SELECT REGEXP_REPLACE(‘Thomas’, ‘.[mN]a.’, ‘M’);
regexp_replace
—————-
ThM
(1 row)
REGEXP_SPLIT_TO_ARRAY()
REGEXP_SPLIT_TO_ARRAY(string text, pattern text [, flags text ]), Split string using a POSIX regular expression as the delimiter.
testdb=# SELECT REGEXP_SPLIT_TO_ARRAY(‘hello world’, E’\s+’);
regexp_split_to_array
———————–
{hello,world}
(1 row)
REGEXP_SPLIT_TO_TABLE()
REGEXP_SPLIT_TO_TABLE(string text, pattern text [, flags text]), splits string using a POSIX regular expression as the delimiter.
testdb=# SELECT REGEXP_SPLIT_TO_TABLE(‘hello world’, E’\s+’);
regexp_split_to_table
———————–
hello
world
(2 rows)
QUOTE_LITERAL(string text)
In the function QUOTE_LITERAL, embedded single-quotes and backslashes are properly doubled.
If a value is passed, coerce the given value to text and then quotes it as a literal.
testdb=# SELECT QUOTE_LITERAL(E’O'Reilly’);
quote_literal
—————
‘O’‘Reilly’
(1 row)
testdb=# SELECT QUOTE_LITERAL(42.5);
quote_literal
—————
‘42.5’
(1 row)
QUOTE_NULLABLE(value anyelement)
The function QUOTE_NULLABLE, coerces the given value to text and then quote it as a literal; or, if the argument is null, returns NULL.
testdb=# SELECT QUOTE_NULLABLE(42.5);
quote_nullable
—————-
‘42.5’
(1 row)
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.
testdb=# SELECT REPEAT(‘SQL’, 3);
repeat
———–
SQLSQLSQL
(1 row)
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.
testdb=# SELECT REPLACE(‘www.mysql.com’, ‘w’, ‘Ww’);
replace
——————
WwWwWw.mysql.com
(1 row)
REVERSE(str)
Returns the string str with the order of the characters reversed.
testdb=# SELECT REVERSE(‘abcd’);
reverse
———
dcba
(1 row)
