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