I need to check in SQL Server how many bytes are used by a value. According to the documentation the DATALENGTH()
function should return values in bytes, but it seems that's not the case. Oracle and Postgres work as expected.
-- Oracle, 3 bytes
SELECT LENGTHB('和');
-- Postgres, 3 bytes
SELECT OCTET_LENGTH('和');
**-- SQL Server, 1 byte
SELECT DATALENGTH('和');**
Should I use another function?
Firstly,
SELECT DATALENGTH('和');
returning1
is correct.'和'
is very likely outside the base codepage you are using, which means that you are effectively asking for theDATALENGTH
of'?'
, and that does only consist of 1 byte.Presumably your Postgres and Oracle environments are in UTF-8, where the character takes up 3 bytes. In UCS-2/UTF-16, however. the character
和
only takes up 2 bytes. When making these comparisons, you need to actually make the test fair; have the data type and code pages all be the same.If you compare the
DATALENGTH
s of your string as an ANSIvarchar
, UCS-2nvarchar
, and a UTF-8varchar
, you get the values1
,2
and3
respectively: