The LENGTH function in SQL returns the length of a string. It is helpful when figuring out how many characters are in a given string.
The function takes one argument, the string whose length you wish to determine. The string length can be any number from 0 to 255.
If the length of the string is 0, then the string will be considered empty and will not be evaluated by the SQL engine.
If the length exceeds 255, the string will truncate to 255 characters.
You can measure the string length in bytes or, more commonly, characters. The most common way is to use the LENGTH function.
SQL Length of String Syntax
The length function will return the length of a string in bytes. The syntax for this function is as follows:
SELECT length(string);
Example:
SELECT length("SQL");
Returns 3
The length function returns 3 since the string has three characters.
SQL LENGTH of String with non-ASCII characters
Note that the length of a string measures the string in terms of bytes, not characters. This can be problematic when working with non-ASCII characters, such as accented letters or Chinese characters. In these cases, the string length will be reported as the number of bytes in the string, not the number of characters.
Example:
SELECT length("大");
Returns 3
Note that the Length() function is a built-in function in MySQL. However, this may not work when using SQL server or Azure. To get around this issue, you can use the DATALENGTH function.
SQL DATALENGTH function
The DATALENGTH function returns the length of a string in terms of bytes, regardless of single-byte or multi-byte characters. The DATALENGTH() function is the equivalent of the LENGTH() function in MySQL.
Example:
SELECT DATALENGTH('SQL');
Returns 3
SELECT DATALENGTH("大");
Returns 3
SQL LEN function
On the other hand, the SQL LEN() function returns the number of characters in a string.
Example:
SELECT Len("SQL");
Returns 3
SELECT Len("大");
Returns 1
Although the Chinese character is multi-byte, the Len() function returns 1. It's because it counts the number of characters and not the bytes.
The Len() function is a built-in SQL server function. The equivalent of the Len() function in the MySQL server is the character length function.
SQL CHARACTER_LENGTH function
This function returns the number of characters in a string, regardless of whether the string contains single-byte or multi-byte characters. It also correctly handles escape characters and other non-printable characters.
Example:
SELECT Char_Length("SQL");
Returns 3
SELECT Char_Length("大");
Returns 1
Just like the Len() function, the Char_Length() function returns 1 for the multi-byte character.
Conclusion
In conclusion, you can measure a string's length in bytes or characters. To measure the String length in bytes, use the MySQL LENGTH function or the SQL server DATALENGTH() function. To measure the length of string in characters, you can use the SQL server LEN function or the MySQL CHARACTER_LENGTH function.
If you enjoy our content, please consider buying us a coffee to support our work:
