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

TypeDescriptionRange of characters
CHAR(X)Contains non-binary strings. The length is fixed, you specify it when declaring. If the length of the string is less than the specified one, it is padded with right spaces to the specified length.The length can be any in the range from 0 to 255
VARCHAR(X)Contains non-binary strings. The length of the lines is dynamic.The length can be any in the range from 0 to 65.535

BINARY and VARBINARY

The BINARY and VARBINARY data types are similar to VARCHAR and CHAR except that they store binary strings.

TypeDescriptionRange of characters
BINARY(X)Contains binary strings. The length is fixed, you specify it when declaring.The length can be any in the range from 0 to 255
VARBINARY(X)Contains binary strings. The length of the lines is dynamic.The length can be any in the range from 0 to 65,535

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.

TypeDescriptionRange of characters
BLOBContains binary strings.Maximum length 65.535
TEXTContains text lines.Maximum length 65.535

BLOB and TEXT have additional subtypes that differ in the maximum data size that can be stored in them.

TypeRange of characters
TINYBLOBMaximum length 255
MEDIUMBLOBMaximum length 16,777,215
LONGBLOBMaximum length 4,294,967,295
TINYTEXTMaximum length 255
MEDIUMTEXTMaximum length 16,777,215
LONGTEXTMaximum length 4,294,967,295

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;
string
sql-academy
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);
CONCAT('sql', '-', 'academy')CONCAT('sql', NULL)
sql-academy<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');
SELECT INSERT('Hello world', 5, 1, ' of the')
Hell of the world
INSTR

The INSTR(str, substr) function returns the position of the occurence of substring substr in string str.

MySQL
SELECT INSTR('sql-academy', 'academy');
INSTR('sql-academy', 'academy')
5
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');
LENGTH('sql-academy')
11
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);
LEFT('sql-academy', 3)RIGHT('sql-academy', 7)
sqlacademy
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);
LOCATE('l', 'Hello world')LOCATE('l', 'Hello world', 5)
310
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');
LOWER('Hello world')UPPER('Hello world')
hello worldHELLO 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');
LPAD('9', 2, '0')RPAD('Hello', 10, 'o')
09Helloooooo
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  ');
TRIM(' Hello ')
Hello
REPEAT

The REPEAT(str, count) function returns a string obtained from count repetitions of str.

MySQL
SELECT REPEAT('101', 3);
REPEAT('101', 3);
101101101
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');
REPLACE('Dear msr Wilson', 'Wilson', 'Brown')
Dear msr Brown
REVERSE

The REVERSE(str) function returns the string str written in reverse order.

MySQL
SELECT REVERSE('drawer');
REVERSE('drawer')
reward
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);
SUBSTRING('sql-academy', 5)
academy

Self-test tasks

Creating tables
Numeric data type