SQL Server If I call Left([col], x) on an NVARCHAR(max) column, is it always safe to save the output in an NVARCHAR(x) column?

1mrurvl1 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(110)

The common misconception that the argument to NVARCHAR(x) is a character count has bitten me many times today. I am asking this question in the interest of exploring the topic further.

Consider an NVARCHAR(max) column. Call it FOO_PERM_COL from table FOO_PERM_TABLE . Will the following code ever fail?

CREATE TABLE #FOO_TEMP_TABLE
(
   FOO_TEMP_COL NVARCHAR(1000) NOT NULL
)

INSERT INTO #FOO_TEMP_TABLE
SELECT Left(FOO_PERM_COL, 1000)
FROM FOO_PERM_TABLE

If the code can fail (i.e. throw an exception), I'd like to see some examples that make it fail. If collations matter, then please say so. If the code cannot fail, then I'd like the argument supporting this to be backed up with reference to the documentation.

I expect that LEFT 's documentation does not tell the full story. The documentation says that it trims your column down to the specified number of characters, but this dbfiddle has me suspect that it does more than that. If LEFT only cut down to the specified number of characters, then I would expect SELECT LEFT(N'🙏', 1) to return the unicode character that you put in. Dbfiddle says that it doesn't. I think that the following line in the documentation is of the utmost relevance to that point
The integer_expression parameter counts a UTF-16 surrogate character as one character.

sql-server

来源：https://stackoverflow.com/questions/77347763/if-i-call-leftcol-x-on-an-nvarcharmax-column-is-it-always-safe-to-save-t

1条答案

按热度按时间

wvyml7n51#

When you create a table having columns with litteral datatypes (CHAR, NCHAR, VARCHAR, NVARCHAR) the database collation is used to enforce the colum datatype.

You can see that in :

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS

SQL Server use UTF16 encoding that have no emitcon by default, but can add emoticons only if you use a SC (Supplmential Characters) collation.

List of SC collations can be found with :

SELECT *
FROM   sys.fn_helpcollations() 
WHERE  description LIKE '%supplementary characters%'

As a test :

CREATE DATABASE DB_TEST_COLLATION COLLATE SQL_EBCDIC277_2_CP1_CS_AS
GO
USE DB_TEST_COLLATION 
GO
SELECT LEFT(N'🙏', 1 )

Will return : �

CREATE DATABASE DB_TEST_COLLATION2 COLLATE Albanian_100_CI_AS_SC
GO
USE DB_TEST_COLLATION2
GO
SELECT LEFT(N'🙏', 1 )

Will RETURN 🙏

CQFD

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server If I call Left([col], x) on an NVARCHAR(max) column, is it always safe to save the output in an NVARCHAR(x) column?

1条答案

相关问题

热门标签

最新问答