sqlserver去掉看不见的特殊字符串char(0) 0x0000

alter FUNCTION dbo.ReplaceCharZero
(
  @testString VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @NR VARBINARY(8000),
            @Len INT,
            @i INT
           
    SET @NR = CAST(@testString AS VARBINARY(8000))
    SET @Len = DATALENGTH(@NR)
    SET @i = 1
    WHILE (@i <= @Len)
        BEGIN
            IF (SUBSTRING(@NR, @i, 1) = 0x00) BREAK
            SET @i = @i + 1
        END

    RETURN CAST(SUBSTRING(@NR, 1, @i - 1) AS VARCHAR(8000))
           
END

测试:

DECLARE @str VARCHAR(8000),@vr VARBINARY(8000)
SET @str='重大资产重组停牌'+char(0)+'你好'
SET @vr = 0xD6D8B4F3D7CAB2FAD6D8D7E9CDA3C5C600C4E3BAC3  --00后面就是乱码
SELECT @str,CAST(@str AS image), LEN(@str),LEN(@vr),DATALENGTH(@vr)
SELECT dbo.ReplaceCharZero(@vr)

说明:

2进制的长度不能用len函数 

0X00之后的字符都是不可用的(乱码或不可见字符)

CAST([Type] AS image)  或者  CAST([Type] AS VARBINARY)  转换成2进制

您可以选择一种方式赞助本站