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;

  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)
  sql	academy
• 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)
  3	10
• 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 world	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');

  LPAD('9', 2, '0')	RPAD('Hello', 10, 'o')
  09	Helloooooo
• 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