MariaDB LENGTH() Function

In MariaDB, LENGTH() is a built-in string function, it returns the length of the given string in bytes in default mode and returns the characters number of the string in Oracle mode.

If you want to get the number of characters in a string, use the CHAR_LENGTH() or CHARACTER_LENGTH() function.

If you want to get the number of bits in a string, use the BIT_LENGTH() function.

LENGTHB() is a synonym for LENGTH() in default mode and has more explicit semantics, so it is recommended to use LENGTHB().

MariaDB `LENGTH()` Syntax

Here is the syntax of the MariaDB LENGTH() function:

LENGTH(str)

Parameters

str: Required. The string whose length needs to be calculated. The parameter can be of other types, such as numbers or dates, etc., and the LENGTH() function will first convert it to a string and then calculate the length.

If you do not provide any parameters, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'LENGTH'.

Return value

In default mode, the MariaDB LENGTH(str) function returns the length in bytes of the specified string, which is the number of bytes.

In Oracle mode, the MariaDB LENGTH(str) function returns the length in characters of the specified string, that is, the number of characters.

If the argument is NULL, the LENGTH() function will return NULL.

MariaDB `LENGTH()` Examples

Basic usage

This statement shows various basic uses of the MariaDB LENGTH() function:

SELECT
    LENGTH('Hello'),
    LENGTH(''),
    LENGTH(20),
    LENGTH(-20),
    LENGTH(+20),
    LENGTH(NOW()),
    LENGTH(CURDATE()),
    LENGTH('你好'),
    LENGTH(NULL)\G

Output:

*************************** 1\. row ***************************
  LENGTH('Hello'): 5
       LENGTH(''): 0
       LENGTH(20): 2
      LENGTH(-20): 3
      LENGTH(+20): 2
    LENGTH(NOW()): 19
LENGTH(CURDATE()): 10
    LENGTH('你好'): 6
     LENGTH(NULL): NULL

Notice:

The result of LENGTH(-20) is 3. TThis is because the corresponding string of -20 is '-20' and LENGTHB('-20') returns 3.
The result of LENGTH(+20) is 2. This is because +20 is equivalent to 20, the corresponding string is '20' and LENGTH('20') returns 2.
LENGTH(NOW()) is equivalent to LENGTH('2021-04-02 21:18:57').
The result of LENGTH('你好') is 6. This is because the encoding I use here is UTF-8 and a Chinese character occupies 3 bytes.

Oracle mode

In Oracle mode, the LENGTH() function will return number of characters of the given string rather than bytes. The following example shows this step by step.

Use the following statement to switch the session to Oracle mode:
```
SET SESSION sql_mode='ORACLE';
```
The following statement uses to LENGTH() calculate the length of '你好':
```
SELECT LENGTH('你好');
```
Output:
```
+------------------+
| LENGTH('你好')   |
+------------------+
|                2 |
+------------------+
```
This time it returns the number of characters (2) instead of the number of bytes (6).

In Oracle mode, LENGTH() is the same as CHAR_LENGTH() or CHARACTER_LENGTH().

Conclusion

In default mode, the MariaDB LENGTH() function return the length of the given string in bytes. In Oracle mode, the LENGTH() function returns the number of characters in the given string.

MariaDB LENGTH() Syntax