REPLACE CHAR(0) NVARCHAR Latin1_General_CI_AS 整理 [英] REPLACE CHAR(0) NVARCHAR Latin1_General_CI_AS collation

问题描述

在 SQL Server 中，我试图用 '' 替换 CHAR(0) 但它不起作用，因为我的字段实际上是 NVARCHAR> 字段，我的排序规则是 Latin1_General_CI_AS 但是当我转换为 SQL_Latin1_General_CP1_CI_AS 时，它只适用于 VARCHAR 而不是 NVARCHAR.

In SQL Server I am trying to replace CHAR(0) with '' but it is not working as my field is actually a NVARCHAR field, my collation is Latin1_General_CI_AS but when I convert to SQL_Latin1_General_CP1_CI_AS it only works for VARCHAR and not NVARCHAR.

SELECT val, REPLACE(val , CHAR(0), 'x')
FROM    (
         SELECT 'a' + CHAR(0) + 'b' AS val
        ) AS X

返回<代码>a |一个

SELECT val, REPLACE(CAST(val COLLATE SQL_Latin1_General_CP1_CI_AS AS varchar(100)), CHAR(0), 'x')
FROM    (
         SELECT 'a' + CHAR(0) + 'b' AS val
        ) AS X

返回<代码>a |斧头

SELECT val, REPLACE(CAST(val COLLATE SQL_Latin1_General_CP1_CI_AS AS varchar(100)), CHAR(0), 'x')
FROM    (
         SELECT CAST('a' + CHAR(0) + 'b' AS NVARCHAR(100)) AS val
        ) AS X
SELECT val, REPLACE(CAST(val COLLATE SQL_Latin1_General_CP1_CI_AS AS nvarchar(100)), CHAR(0), 'x')

返回<代码>a |斧头

FROM    (
         SELECT CAST('a' + CHAR(0) + 'b' AS NVARCHAR(100)) AS val
        ) AS X
SELECT val, REPLACE(CAST(val COLLATE SQL_Latin1_General_CP1_CI_AS AS varchar(100)), nCHAR(0), 'x')

返回<代码>a |一个

FROM    (
         SELECT CAST('a' + CHAR(0) + 'b' AS NVARCHAR(100)) AS val
        ) AS X
SELECT val, REPLACE(CAST(val COLLATE SQL_Latin1_General_CP1_CI_AS AS nvarchar(100)), nCHAR(0), 'x')

返回<代码>a |一个

FROM    (
         SELECT CAST('a' + CHAR(0) + 'b' AS NVARCHAR(100)) AS val
        ) AS X

返回<代码>a |一个

有没有办法用 '' 替换 0x0000 字符而不丢失 NVARCHAR 状态

Is there a way that I can replace the 0x0000 characters with '' and not lose my NVARCHAR state

预期值为 a |axb 用于最后一个查询(或类似但将支持 NVARCHAR 的查询.

the expected value is a | axb for the last query (or one that is similar but will support NVARCHAR.

推荐答案

使用二进制 collat​​e 子句，无需CAST

Use a binary collate clause and there is no need to CAST

;WITH T
     AS (SELECT CAST('a' + CHAR(0) + 'b' AS NVARCHAR(100)) AS val)
SELECT val,
       REPLACE(val COLLATE Latin1_General_100_BIN, nCHAR(0), 'x')
FROM   T 

这篇关于REPLACE CHAR(0) NVARCHAR Latin1_General_CI_AS 整理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！