Home / SQL Statements / Question No: 221

Literal strings in expressions are written as quoted values. By default, either single quotes or double quotes can be used, although single quotes are more standard. Also, if the ANSI_QUOTES SQL mode is enabled, double quotes are interpreted as identifier-quoting characters, so literal strings can be quoted only with single quotes.

The data types for representing strings in tables include CHAR, VARCHAR, BINARY, VARBINARY, and the TEXT and BLOB types. You choose which type to use depending on factors such as the maximum length of values, whether you require fixed-length or variable-length values, and whether the strings to be stored are non-binary or binary. Direct use of strings in expressions occurs primarily in comparison operations. Otherwise, most string operations are performed by using functions.

The usual comparison operators apply to string values (=, <>, <, BETWEEN, and so forth). The result of a comparison depends on whether strings are non-binary or binary and, for non-binary strings that have the same character set, on their collation. (A comparison between strings that have different character sets typically results in an error.)

String concatenation is done with the CONCAT() function:

mysql> SELECT CONCAT('abc','def',REPEAT('X',3));
+-----------------------------------+
| CONCAT('abc','def',REPEAT('X',3)) |
+-----------------------------------+
| abcdefXXX                         |
+-----------------------------------+

The || operator is treated as the logical OR operator by default, but can be used for string concatenation if you enable the PIPES_AS_CONCAT SQL mode:

mysql> SELECT 'abc' || 'def';
+----------------+
| 'abc' || 'def' |
+----------------+
|              0 |
+----------------+


mysql> SET sql_mode = 'PIPES_AS_CONCAT';

mysql> SELECT 'abc' || 'def';
+----------------+
| 'abc' || 'def' |
+----------------+
| abcdef         |
+----------------+

In the first SELECT statement, || performs a logical OR operation. This is a numeric operation, so MySQL converts the strings in the expression to numbers first. Neither looks like a number, so MySQL converts them to zero, which is why there is a warning count of two. The resulting operands for the operation are zero, so the result also is zero. After PIPES_AS_CONCAT is enabled, || produces a string concatenation instead.

There are several functions take string arguments or return string values. Some types of operations these functions can perform are to convert lettercase, calculate string lengths, or search for, insert, or replace substrings.