How to calculate the length of Chinese string in SQL

In SQL, the calculation of string length depends on the data type: VARCHAR and NVARCHAR: in bytes, one character occupies 1 or 2 bytes. CHAR and NCHAR: One character always occupies a fixed length in terms of number of characters (CHAR 1 byte, NCHAR 2 bytes). The LEN function returns the length in bytes (VARCHAR, NVARCHAR) or characters (CHAR, NCHAR) of a string.

Calculation of string length in SQL

In SQL, the way string length is calculated depends on the characters String data type.

VARCHAR and NVARCHAR

For VARCHAR and NVARCHAR types, the string length is measured in bytes. A character may occupy 1 byte (ANSI character set) or 2 bytes (Unicode character set).

LEN(string) The function returns the length in bytes of the string.

CHAR and NCHAR

For the CHAR and NCHAR types, string length is measured in characters, not bytes. Each character always occupies a fixed length:

• CHAR: 1 byte
• NCHAR: 2 bytes

LEN( string) The function returns the character length of the string.

Example

The following example demonstrates how to calculate the length of different string types:

DECLARE @varchar_str VARCHAR(10) = 'Hello';
DECLARE @nvarchar_str NVARCHAR(10) = N'你好';
DECLARE @char_str CHAR(5) = 'World';
DECLARE @nchar_str NCHAR(5) = N'世界';

SELECT LEN(@varchar_str) AS VarcharLength; -- 5
SELECT LEN(@nvarchar_str) AS NvarcharLength; -- 10
SELECT LEN(@char_str) AS CharLength; -- 5
SELECT LEN(@nchar_str) AS NcharLength; -- 5

It is worth noting that string interception functions (such as SUBSTRING) And concatenation functions (such as CONCAT) automatically adjust the length of the returned result based on the string type.

The above is the detailed content of How to calculate the length of Chinese string in SQL. For more information, please follow other related articles on the PHP Chinese website!