MySQL Database learning



Home / SQL Statements / How to use string expressions in MySQL?
Share this job:


How to use string expressions in MySQL?

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.





SQL Statements related other questions

What is difference between delete and truncate statements of MySQL?

Logically, TRUNCATE statement is equivalent to a DELETE statement that deletes all rows from the table, but there are important differences in some cases.  The following list of TRUNCATE and DELETE state ...

What does alias means in MySQL?

When you join tables, it's often the case that the tables contain columns with the same names. If you refer to such a column in the query, it's ambiguous which table the column reference applies to. This ambi ...

What does SQL Expressions mean in MySQL?

Expressions are a common element of SQL statements, and they occur in many contexts. For example, expressions often occur in the WHERE clause of SELECT, DELETE, or UPDATE statements to identify which records ...

How to use numeric expressions in MySQL?

Numbers can be exact-value literals or approximate-value literals. Exact-value literals are used just as given in SQL statements when possible and thus are not subject to the inexactness produced by rounding ...

How to use string expressions in MySQL?

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 enabl ...

How to manage case sensitivity in string comparisons?

String comparisons are somewhat more complex than numeric or temporal comparisons. Numbers sort in numeric order and dates and times sort in temporal order, but string comparisons depend not only on the speci ...

How to use LIKE pattern-matching operator?

Operators such as = and != are useful for finding values that are equal to or not equal to a specific exact comparison value. When it's necessary to find values based on similarity instead, a pattern match is ...

How to use date or temporal expressions?

Temporal values include dates, times, and datetime values that have both a date and time. More specialized temporal types are timestamp (commonly used for recording "current date and time") and year (for temp ...

What does NULL value mean in MySQL?

NULL is unusual because it doesn't represent a specific value the way that numeric, string, or temporal values do. Instead, NULL stands for the absence of a known value. The special nature of NULL means that ...

How to use comments in SQL statements?

MySQL supports three forms of comment syntax. One of those forms has variants that allow special instructions to be passed through to the MySQL server. A '#' character begins a comment that ext ...

How to validate user login in case sensitive manner?

MySQL provides more than one mothed to do so: You can set your concerned column's COLLATION to latin_general_cs. If you don't like above, you can force MySQL to check for an exact cas ...