In MySQL, the DATE_FORMAT() function allows you to format the date and time.
Here’s an example:
SELECT DATE_FORMAT('2018-12-01', '%W, %d %M %Y');
Result:
Saturday, 01 December 2018
In this example, %W is for the weekday name, %d is for the day of the month, %M is for Month, and %Y is for Year. There are many more format specifiers available that enable you to specify a precise format for dates, as well as the time component.
MySQL has a number of functions that allow you to format the date and time. These include functions such as DATE_FORMAT() and TIME_FORMAT().
When using these functions, you provide arguments that tell it what format you’d like the date or time to be in. These arguments are called format specifiers, and they begin with a percentage sign (%) followed by a character (usually a letter, but not always).
The following table lists the format specifiers that can be used when formatting the date and time in MySQL.
In MySQL, you can use the FORMAT() function to format a number to a specific format. This function accepts three parameters; the number, the number of decimal places, and an optional locale.
In MySQL, the CONCAT_WS() function allows you to add a separator to concatenated strings. If you just use the CONCAT() function, you’d have no separator (unless you explicitly added a separator as an argument between each string argument).
A common usage of the CONCAT_WS() function is to create a comma-delimited list.
MySQL has the CONCAT() function, which allows you to concatenate two or more strings. The function actually allows for one or more arguments, but its main use is to concatenate two or more strings.
In MySQL (and in any computer programming environment), string concatenation is the operation of joining character strings end-to-end.
Here’s an example:
SELECT CONCAT('Homer', ' ', 'Simpson') AS 'Full Name';
Result:
+---------------+ | Full Name | +---------------+ | Homer Simpson | +---------------+
Note that I actually concatenated 3 strings here. I concatenated the first name, the last name, plus a space.
In SQL Server, you can use the LEN() function to return the number of characters in a string. This is a Transact-SQL function that can also be used in Azure databases.
In MySQL, you need to use the CHAR_LENGTH() function.
If you’ve been working with SQL Server for any length of time, you might have encountered the Datalength() function. This function returns the number of bytes used to represent an expression.
But if you’ve moved to MySQL, you may be looking for a function that does the same thing.
In MySQL, the Length() function does basically the same thing that the T-SQL Datalength() function does in SQL Server (and Azure). MySQL’s Length() function returns the length of a string, measured in bytes.
Continue reading
In MySQL, there are many times where the length() function and the char_length() function will provide exactly the same results. However, there are also times where the results will be completely different. Here’s why.
First, here’s the definition for each of these functions:
char_length()length()Notice “characters” vs “bytes” – one is measured in characters, the other is measured in bytes.
In many cases, the number of bytes will be the same as the number of characters in the string, but this isn’t always the case. The number of bytes used per character depends on how the data is stored. For example, if the string is stored as Unicode data, there will be 2 bytes per character.
Continue reading
In MySQL, the CHAR_LENGTH() function returns the length of a string, measured in characters. MySQL also has CHARACTER_LENGTH(), which is a synonym for CHAR_LENGTH().
Here’s an example:
SELECT CHAR_LENGTH('Lit');
And here’s the result:
+--------------------+
| CHAR_LENGTH('Lit') |
+--------------------+
| 3 |
+--------------------+
One of the many functions in MySQL is the LENGTH() function, which returns the length of a string, measured in bytes.
Example:
SELECT LENGTH('Lit');
Result:
+---------------+
| LENGTH('Lit') |
+---------------+
| 3 |
+---------------+
This is a simple example and the result is the same as if we’d used the CHAR_LENGTH() function. However, the LENGTH() function can return different results, depending on the data type.
Continue reading