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.
Category: MySQL
How to Add a Separator to a Concatenated String in MySQL – CONCAT_WS()
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.
How to Concatenate Strings in MySQL with CONCAT()
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.
What’s the LEN() Equivalent in MySQL?
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.
What’s the DATALENGTH() Equivalent in MySQL?
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
MySQL length() vs char_length()
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()- Returns the length of a string, measured in characters.
length()- Returns the length of a string, measured in bytes.
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
What is CHAR_LENGTH() in MySQL?
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 |
+--------------------+
What is LENGTH() in MySQL?
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
How to Set the Character Set and Collation of a Database in MySQL
In MySQL, you can specify the character set and collation at various levels. You can specify them at the connection level, the server level, the database level, the table level, and the column level. You can also specify a collation in your queries so that it overrides any collation that has been previously specified at the aforementioned levels.
To set the character set and collation at the database level, you can use the CREATE DATABASE statement or ALTER DATABASE statement (depending on whether you’re creating the database or modifying it).
Continue reading
How to Set the Character Set and Collation of a Table in MySQL
In MySQL, you can specify the character set and collation at various levels. You can specify them at the connection level, the server level, the database level, the table level, and the column level. You can also specify a collation in your queries so that it overrides any collation that has been previously specified at the aforementioned levels.
To set the character set and collation at the table level, you can use the CREATE TABLE statement or ALTER TABLE statement (depending on whether you’re creating the table or modifying it).
Continue reading