如何为varchar(max)字段使用QUOTENAME？查询返回NULL问题排查

Alright, let's break down your two QUOTENAME-related questions step by step:

The key limitation to know here is that QUOTENAME's first parameter is typed as sysname (equivalent to nvarchar(128) under the hood). This means if your varchar(max) value is longer than 128 characters, the built-in function will return NULL instead of the quoted string you expect. Here's how to handle it:

• If your content is ≤128 characters: You can use QUOTENAME directly, just like with shorter varchar types. For example:

  SELECT QUOTENAME(your_varchar_max_column) FROM your_table;
  

  SQL will implicitly convert your varchar(max) value to nvarchar(128) here, which works fine as long as the length stays within the limit.

• If your content is >128 characters: You'll need to manually replicate QUOTENAME's behavior to support longer strings. The function automatically escapes existing delimiters (e.g., turning ] into ]] when using square brackets), so you’ll want to mirror that logic:
  For square brackets (QUOTENAME's default):

  SELECT '[' + REPLACE(your_varchar_max_column, ']', ']]') + ']' AS QuotedColumn
  FROM your_table;
  

  For single quotes (if you’d normally use QUOTENAME(col, '''')):

  SELECT '''' + REPLACE(your_varchar_max_column, '''', '''''') + '''' AS QuotedColumn
  FROM your_table;
  

The NULL result here stems from the same 128-character input limit in QUOTENAME. Since your col1 is varchar(2000), any row where the content length exceeds 128 characters will cause the function to return NULL for that row. Here’s how to resolve it:

1. Confirm the length issue first: Run this query to identify rows with content longer than 128 characters:

   SELECT col1, LEN(col1) AS ColumnLength
   FROM table1
   WHERE LEN(col1) > 128;
   

2. Replace QUOTENAME with a manual implementation: To handle all rows (short and long) consistently, use the delimiter-escaping method from the first question:

   SELECT '[' + REPLACE(col1, ']', ']]') + ']' AS QuotedCol1
   FROM table1;
   

3. Hybrid approach (optional): If you prefer using QUOTENAME for short strings (for minor performance gains) and the manual method for long ones, use a CASE statement:

   SELECT 
       CASE 
           WHEN LEN(col1) <= 128 THEN QUOTENAME(col1)
           ELSE '[' + REPLACE(col1, ']', ']]') + ']'
       END AS QuotedCol1
   FROM table1;
   

内容的提问来源于stack exchange，提问作者Guna