String Expressions

           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 and the TEXT and BLOB types. The type chosen to be used depends on factors such as the maximum length of values and whether fixed-length or variable-length values are required.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):

Operator Definition


<                                   Less than
<=                                 Less than or equal to
=                                   Equal to
<=>                              Equal to (works even for NULL values)
<>                                or != Not equal to
>=                                Greater than or equal to
>                                  Greater than


BETWEEN
<x AND Y>
                                    Indicate a range of numerical values





CONCAT


String concatenation is done with the CONCAT() function:


mysql> SELECT CONCAT('abc','def');
+----------------------------+
| CONCAT('abc','def') |
+----------------------------+
| abcdef                        |
+----------------------------+


some more example



mysql> SELECT concat(name, ' is in ', district) FROM city LIMIT 5;
+---------------------------------------+
| concat(name, ' is in ', district) |
+---------------------------------------+
| Kabul is in Kabol                     |
...
5 rows in set (#.## sec)



OR (||) or CONCAT

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

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


1 row in set, 2 warnings (#.## sec)


mysql> SET sql_mode = 'PIPES_AS_CONCAT';
Query OK, 0 rows affected (#.## sec)


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.