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.

Examples

Here are some examples to demonstrate.

SQL Server

Here’s a basic example for SQL Server (using T-SQL):

SELECT DATALENGTH('Cat') AS Datalength;

Result:

Datalength
----------
3         

MySQL

Here’s the equivalent query in MySQL:

SELECT LENGTH('Cat') AS Length;

Result:

+--------+
| Length |
+--------+
|      3 |
+--------+

Both Measure Bytes

But most importantly, both functions measure bytes, not characters. So if the data is stored as a Unicode string, the number of bytes will be double the number of characters. This is because Unicode strings use 2 bytes per characters.

Below is an example of running essentially the same query against a Unicode column in SQL Server, then in MySQL.

SQL Server

SELECT DATALENGTH(ArtistName) AS DataLength
FROM Artists 
WHERE ArtistName = 'Lit';

Result:

DataLength
----------
6         

MySQL

SELECT LENGTH(ArtistName) AS Length
FROM Artists
WHERE ArtistName = 'Lit';

Result:

+--------+
| Length |
+--------+
|      6 |
+--------+

It’s important to note that the reason these examples return 6 is because of the data type used in the database. Both examples could also return 3 if the data wasn’t stored as Unicode.

Characters vs Bytes

In the SQL Server example, if we’d used the Len() function instead, it would’ve returned 3. This is because Len() returns the number of characters – not the bytes.

Similarly for MySQL, if we’d used the Char_Length() function, it would also have returned 3, because Char_Length() returns the number of characters instead of bytes.