在sql中替换字符串中的空字符

I need to replace a null character in a sql string, i cant seem to find the right command to achieve this. I have used replace (myString ,'\0', '') but this seems not to work, any help would be great

我需要在一个sql字符串中替换一个null字符，我似乎找不到正确的命令来实现它。我已经使用了replace (myString，'\0'， ")，但这似乎行不通，任何帮助都是很好的

9 个解决方案

#1

Use this:

用这个:

REPLACE(myString, char(0), '')

#2

The trick that works is to COLLATE your value to Latin1_General_BIN before using REPLACE and also use nchar(0x00) COLLATE Latin1_General_BIN for string_pattern.

有用的技巧是在使用REPLACE之前先将值排序为Latin1_General_BIN，还可以使用nchar(0x00)排序为string_pattern的Latin1_General_BIN。

REPLACE ( string_expression , string_pattern , string_replacement )

替换(string_expression、string_pattern、string_replace)

 select 
 [Terminated]      =          N'123' + nchar(0) + N'567'                                
,[Replaced with -] = REPLACE((N'123' + nchar(0) + N'567') COLLATE Latin1_General_BIN
                                          , nchar(0x00) COLLATE Latin1_General_BIN 
                                                 ,'-')      
,[Removed]        = REPLACE((N'123' + nchar(0) + N'567') COLLATE Latin1_General_BIN
                                    , nchar(0x00)      COLLATE Latin1_General_BIN
                                            ,'')    

Here is the result (use Output To Text):

这里是结果(使用输出到文本):

Contains   Replaced with -   Removed
---------- ----------------- --------
123 567    123-567           123567

#3

For latin characters: select REPLACE('Ho'+CHAR(0)+'mer' COLLATE SQL_Latin1_General_CP1_CS_AS, CHAR(0), '')

对于拉丁字符:选择REPLACE('Ho'+CHAR(0)+'mer' COLLATE SQL_Latin1_General_CP1_CS_AS, CHAR(0)， ")

For russian characters: select REPLACE(('Го'+CHAR(0)+'мер') COLLATE Cyrillic_General_BIN , CHAR(0), '')

对俄罗斯字符:选择替换((“Го”+字符(0)+мер)核对Cyrillic_General_BIN,CHAR(0)”)

#4

I'm not completely sure what is wrong with your strings, but here are some things to try, are you using varchar?, edit question with more details:

我不太确定你的琴弦出了什么问题，但这里有一些东西可以尝试，你在用varchar吗?，编辑问题的更多细节:

if you have NULL characters within a string:

如果字符串中有空字符:

declare @x varchar(10)
set @x='123'+char(0)+'456'
SELECT @x AS Has_NULL_in_it, REPLACE(@x, char(0), '') AS Has_NULL_removed

OUTPUT:

输出:

Has_NULL_in_it Has_NULL_removed
-------------- ----------------
123 456        123456

(1 row(s) affected)

If you can't tell the character within the string, try this ASCII:

如果你不能告诉字符串中的字符，试试这个ASCII:

DECLARE @y varchar(10),@c int
set @y='123'+char(0)+'456'
set @c=0
WHILE @c<LEN(@y)
BEGIN
    SET @c=@c+1
    PRINT CONVERT(varchar(5),@c)+' - '+SUBSTRING(@y,@c,1)+' - CHAR('+CONVERT(varchar(5),ASCII(SUBSTRING(@y,@c,1)))+')'
END

OUTPUT:

输出:

1 - 1 - CHAR(49)
2 - 2 - CHAR(50)
3 - 3 - CHAR(51)
4 - - CHAR(0)
5 - 4 - CHAR(52)
6 - 5 - CHAR(53)
7 - 6 - CHAR(54)

try this unicode:

试试这个unicode:

DECLARE @y nvarchar(10),@c int
set @y='123'+char(0)+'456'
set @c=0
WHILE @c<LEN(@y)
BEGIN
    SET @c=@c+1
    PRINT CONVERT(nvarchar(5),@c)+' - '+SUBSTRING(@y,@c,1)+' - UNICODE('+CONVERT(nvarchar(5),UNICODE(SUBSTRING(@y,@c,1)))+')'
END

if your have strings that are completely NULL:

如果你的字符串是完全空的:

declare @z varchar(10)
set @z=NULL
select @z AS IS_NULL, ISNULL(@Z,'') AS NULL_Removed

OUTPUT:

输出:

IS_NULL    NULL_Removed
---------- ------------
NULL       

(1 row(s) affected)

#5

If you are concatenating values to get your string use IsNull(value, replacement) to avoid having null values or set CONCAT_NULL_YIELDS_NULL ON to avoid null strings as a result.

如果您正在连接值以使您的字符串使用IsNull(值，替换)来避免空值，或者设置CONCAT_NULL_YIELDS_NULL ON以避免空字符串。

#6

If you Only have ASCII (Char/VarChar) strings then this will work as @DyingCactus suggests:

如果您只有ASCII (Char/VarChar)字符串，那么这将会像@DyingCactus所建议的那样:

REPLACE(myString, Char(0x00), '')

However, if you are dealing with Null-Terminated Strings and are trying to fix or convert to something like XML, and your data is Unicode (nChar/nVarChar), then use this:

但是，如果您正在处理以null结尾的字符串，并试图修复或转换为XML之类的东西，并且您的数据是Unicode (nChar/nVarChar)，那么请使用以下方法:

(CASE WHEN UNICODE(SUBSTRING(myString, LEN(myString), 1)) = 0x0000
      THEN SUBSTRING(myString, 1, LEN(myString) - 1)
      ELSE myString END)

This works for both ASCII (Char/VarChar) and Unicode (nChar/nVarChar).

这适用于ASCII (Char/VarChar)和Unicode (nChar/nVarChar)。

Note

Using the Replace() function with Char(0) or nChar(0) will NOT work for Unicode (nChar/nVarChar).
It's a bug in the SQL Server Replace() function.

You could cast as VarChar, then use Replace(), but then you would lose any special Unicode/Non-ASCII characters you might have intended to keep.
Otherwise you wouldn't have used the Unicode datatype (that takes up twice as much space to store your data) in the first place.

If you have Null-Characters mixed in with your Unicode strings (and not only at the end), and, for the purposes of your query, maintaining Unicode-specific characters are unimportant, then as a last resort you could use this :

使用带有Char(0)或nChar(0)的Replace()函数不能用于Unicode (nChar/nVarChar)。这是SQL Server Replace()函数中的一个错误。您可以将其转换为VarChar，然后使用Replace()，但是这样会丢失您可能希望保留的任何特殊的Unicode/非ascii字符。否则，您就不会首先使用Unicode数据类型(它占用两倍的空间来存储数据)。如果将空字符与Unicode字符串混合(不仅在末尾)，并且为了查询的目的，维护特定于Unicode的字符是不重要的，那么作为最后一招，您可以使用以下方法:

(CASE WHEN myString LIKE (N'%' + nCHAR(0x0000) + N'%')--Has Null-Character(s).
      THEN REPLACE(CAST(myString as VarChar(MAX)), Char(0x00), '')--Cast as ASCII
      ELSE myString END)--Else, leave as Unicode to preserve Unicode-Only chars.

#7

These functions remove null characters from Unicode strings, at least in SQL Server 2008.

这些函数从Unicode字符串中删除空字符，至少在SQL Server 2008中是这样。

-- Remove all null characters
CREATE FUNCTION RemoveNulls(@s nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @r nvarchar(max);
    SET @r = REPLACE(@s COLLATE Latin1_General_BIN, NCHAR(0), N'');
    RETURN @r; 
END

-- Remove all characters from the first null character
CREATE FUNCTION TrimNull(@s nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @r nvarchar(max);
    DECLARE @i int = CHARINDEX(NCHAR(0), @s COLLATE Latin1_General_BIN);
    IF @i = 0
        SET @r = @s;
    ELSE
        SET @r = SUBSTRING(@s, 1, @i - 1);
    RETURN @r; 
END

-- Example usage
DECLARE @s nvarchar(10) = N'Test' + NCHAR(0) + N'!';
SELECT dbo.RemoveNulls(@s), dbo.TrimNull(@s);
--> Test!, Test

In my case, fields from ODBC were padded to 8000 characters with null and TrimNull was much faster than RemoveNulls.

在我的例子中，ODBC的字段被填充到具有null的8000个字符中，并且TrimNull比RemoveNulls快得多。

#8

We had the same problem: Ending \0 character in nvarchar fields and unable to replace it with any of the REPLACE variants proposed (SQL Server 2008). When using

我们遇到了相同的问题:在nvarchar字段中结束\0字符，并且无法用所提议的任何替换变体替换它(SQL Server 2008)。当使用

LEFT(Bar, LEN(Bar)-1)

it cut off the last regular character together with the \0 !

它与\0一起切断最后一个规则字符!

Our solution now to correct the fields is (as weird as it may seem on a first glimpse):

我们现在纠正这些字段的方法是(就像第一眼看到的那样奇怪):

UPDATE Foo
    SET Bar = LEFT(Bar, LEN(Bar))
WHERE RIGHT(Bar, 1) = CHAR(0)

#9

Examples resolved

例子解决

CREATE FUNCTION dbo.F_ReplaceNullChar( @STR NVARCHAR(MAX) )
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @i INT=0
DECLARE @RET NVARCHAR(MAX)=''
    WHILE @I<LEN(@STR) 
    BEGIN 
        SET @i=@i+1
        IF UNICODE(SUBSTRING(@STR,@i,1)) <> 0x0000
            SET @RET=@RET+SUBSTRING(@STR,@i,1)

    END
    RETURN @RET
END
GO

SELECT LEN(mycol) lenbefore,mycol,
 LEN( dbo.F_ReplaceNullChar(mycol)) lenafter, dbo.F_ReplaceNullChar(mycol) mycolafter 
FROM mytab