LEN Function in MS SQL Server and it's counterpart in MySQL

Then LEN function in MSSQL returns the length of a given string. Whereas in MySQL LENGTH function achieves the same result. But there is a catch. The LEN function in MSSQL returns the number of characters in a string excluding the right most blank spaces. The MySQL function returns the number of characters including the right most blank spaces.

In MSSQL there is another function by name DATALENGTH which returns the number of characters in a string without excluding the trailing blank spaces.

Here are the examples

MSSQL

select len('demo ')

-----------------------
4

select datalength('demo  ')

------------------------
6

MySQL

In MySQL the length function has the same functionality but it doesn't trim trailing blank spaces. It returns the length of the string in bytes. So it will not be suitable for unicode character strings since they are multibyte. You can use CHAR_LENGTH function in MySQL to just count the number of characters irrespective of single byte or multibyte characters.

Here are examples

mysql> select length('demo   ');
+------------------+
| length('demo   ')|
+------------------+
| 7 |
+------------------+
1 row in set (0.00 sec)

mysql> select char_length('demo ');
+------------------------+
| char_length('demo ') |
+------------------------+
| 7 |
+------------------------+
1 row in set (0.00 sec)

mysql>

Here is the comparison of LENGTH and CHAR_LENGTH function for unicode strings

Back to Converting Functions from MSSQL to MySQL