Log in
String data type
The string data type is the most commonly used data type. Thanks to this, both text and various binary data (for example, pictures) are stored in the database.
In MySQL, it is represented by the following types:
CHAR and VARCHAR
BINARY and VARBINARY
The BINARY and VARBINARY data types are similar to VARCHAR and CHAR except that they store binary strings.
BLOB и TEXT
BLOB is used to store large binary data such as pictures.TEXT is also for storing big data, but textual content.
The difference between them is that the sorts and comparisons of stored data for BLOBs are case sensitive and not case sensitive in TEXT fields.
BLOB and TEXT have additional subtypes that differ in the maximum data size that can be stored in them.
Popular functions for working with strings
CHAR
The CHAR(char1, char2, ...) function takes a sequence of ASCII codes and returns the results created by concatenating their corresponding characters.
MySQL
SELECT CHAR(115,113,108,45,97,99,97,100,101,109,121) as string;
CONCAT
The CONCAT(str1, str2, ...) functions returns a string created by concatenation all arguments, the number of which is not limited. If one of the arguments is NULL,NULL is returned.
MySQL
SELECT CONCAT('sql', '-', 'academy'), CONCAT('sql', NULL);
INSERT
The INSERT(str, pos, len, newStr) functions returns the string str in which the substring staring at position pos and have length len characters has been replaced by the substring newStr. The function returns the string str unchanged if pos is outside the string.
MySQL
SELECT INSERT('Hello world', 5, 1, ' of the');
INSTR
The INSTR(str, substr) function returns the position of the occurence of substring substr in string str.
MySQL
SELECT INSTR('sql-academy', 'academy');
LENGTH
The LENGTH(str) function returns the length of the string str. This function may work incorrectly with multibyte encodings, because the number of bytes in the string is actually returned.
MySQL
SELECT LENGTH('sql-academy');
LEFT, RIGHT
The LEFT(str, len) function returns len of the leftmost characters of str/ if len is 0 или NULL, an empty string is returned. The RIGHT(str, len) function is similar to the LEFT(str, len), function, except that it returns len of the rightmost characters.
MySQL
SELECT LEFT('sql-academy', 3), RIGHT('sql-academy', 7);
LOCATE
The LOCATE(substr, str [,pos]) function returns the position of the first occurrence of substring substr in string str. If no matches are found, 0 is returned. With the optional pos argument, the search starts at the position specified in this argument.
MySQL
SELECT LOCATE('l', 'Hello world'), LOCATE('l', 'Hello world', 5);
LOWER, UPPER
The LOWER(str) and UPPER(str) functions return the string str in which all characters are written in lowercase and uppercase, respectively.
MySQL
SELECT LOWER('Hello world'), UPPER('Hello world');
LPAD, RPAD
The LPAD(str, len, padStr) function returns str, left-padded with padStr to the specified length len. The RPAD(str, len, padStr) function pads the string to the right.
MySQL
SELECT LPAD('9', 2, '0'), RPAD('Hello', 10, 'o');
LTRIM, RTRIM, TRIM
The LTRIM(str) function returns the string str with all left-side spaces removed. The RTRIM(str) function removes spaces from the right margin. The TRIM(str) function combines these functions.
MySQL
SELECT TRIM(' Hello ');
REPEAT
The REPEAT(str, count) function returns a string obtained from count repetitions of str.
MySQL
SELECT REPEAT('101', 3);
REPLACE
The REPLACE(str, fromStr, toStr) function returns the string str in which all substrings fromStr have been replaced by toStr.
MySQL
SELECT REPLACE('Dear msr Wilson', 'Wilson', 'Brown');
REVERSE
The REVERSE(str) function returns the string str written in reverse order.
MySQL
SELECT REVERSE('drawer');
SUBSTRING
The SUBSTRING(str, pos [,len]) function returns the substring of str that starts at position pos and is len characters long. If the third parameter len is omitted, then the substring is returned from pos to the end of str.
MySQL
SELECT SUBSTRING('sql-academy', 5);
Self-test tasks
Creating tables
Numeric data type