What’s the DATALENGTH() Equivalent in MySQL?

Posted on by Ian

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()

Posted on by Ian

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 LENGTH() in MySQL?

Posted on by Ian

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 →

LEN() vs DATALENGTH() in SQL Server

Posted on by Ian

When using T-SQL in SQL Server (or Azure) the LEN() and DATALENGTH() functions will often return the same result, but not always. There are some cases where these functions will return completely different results for what appears to be the same data. This is because there’s an important difference between how the LEN() and DATALENGTH() functions work, as we’ll see here.

First up, here’s a quick definition of each:

LEN()
Returns the number of characters of the specified string expression, excluding trailing blanks.
DATALENGTH()
Returns the number of bytes used to represent any expression.

Note “characters” vs “bytes”. Also note that “excluding trailing blanks” only applies to one.

Here are some examples to demonstrate the differences between LEN() and DATALENGTH().
Continue reading →

How to Install SQL Server on a Mac with VirtualBox

Posted on by Ian

Here I’ll show you how to install SQL Server on a Mac with VirtualBox running Windows (a free trial edition).

The result of this is that you’ll have both Windows and SQL Server running on your Mac. And because you’re using VirtualBox, switching between macOS and Windows is as easy as switching between any other application.

As mentioned, this method involves Windows. If that scares you, then you might be better off installing SQL Server on your Mac via a Docker container. That method doesn’t involve Windows in any shape or form.

But if you don’t mind using Windows (or if you need to), here are the main steps for installing SQL Server for Windows on your Mac using VirtualBox:

  1. Download and Install VirtualBox
  2. Create a New Virtual Machine (VM)
  3. Download the Windows ISO image
  4. Install Windows
  5. Download and Install SQL Server

Below are more detailed instructions for each of these steps.
Continue reading →

How to Set the Character Set and Collation of a Database in MySQL

Posted on by Ian

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

Posted on by Ian

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 →