我想知道在TSQL中Null character的字面量是什么(例如'\0')。
注意:不是空字段值,而是空字符(see link)。
我有一个混合了典型字符和空字符的列。我正在尝试用不同的值替换空字符。我本以为下面的方法会行得通,但没有成功:
select REPLACE(field_with_nullchar, char(0), ',') from FOO where BAR = 20
发布于 2010-05-14 00:41:36
看起来C风格的终止符也是SQL中的终止符:
SELECT REPLACE(bad, CHAR(0), ' ')
FROM (
SELECT 'a' + CHAR(0) + 'b' AS bad
) AS X
看起来它也依赖于排序规则:
SELECT REPLACE(CAST(bad COLLATE SQL_Latin1_General_CP1_CI_AS AS varchar(10)), CHAR(0), ' ')
FROM (
SELECT 'a' + CHAR(0) + 'b' AS bad
) AS X
与预期相比,工作正常:
SELECT REPLACE(CAST(bad COLLATE Latin1_General_CI_AS AS varchar(10)), CHAR(0), ' ')
FROM (
SELECT 'a' + CHAR(0) + 'b' AS bad
) AS X
发布于 2010-05-14 19:42:25
VARBINARY
强制转换应适用于任何排序规则
SELECT
REPLACE(CAST(CAST(fld AS VARCHAR(5)) AS VARBINARY(5)), 0x0, ',')
FROM
(SELECT 'QQ' + CHAR(0) + 'WW' COLLATE Latin1_General_CI_AS AS fld) AS T
SELECT
REPLACE(CAST(CAST(fld AS VARCHAR(5)) AS VARBINARY(5)), 0x0, ',')
FROM
(SELECT 'QQ' + CHAR(0) + 'WW' COLLATE SQL_Latin1_General_CP1_CI_AS AS fld) AS T
>>QQ,WW
>>QQ,WW
发布于 2010-05-14 00:42:47
您确定它们是空字符吗?你是怎么把它们弄进去的?
看起来SQL Server将它们视为字符串终止符。此查询:
select 'aaa' + char(0) + 'bbb'
为我返回aaa
(在SQL Server2008上)。
编辑:上面的是错误的-只是结果网格这样对待它们。它们以文本模式显示。
https://stackoverflow.com/questions/2828333
复制相似问题