I have a string of type VARCHAR(128) in MS SQL Server, which has this value: 'abc '.
However, these trailing "spaces" are not regular spaces (ASCII 32), they are of ASCII 0. I want to trim these trailing "spaces", but I could not get it to work.
I have tried the following approaches:
ltrim and rtrim
replace('abc ', CHAR(0), '') substring('abc ', 0, charindex(CHAR(0), 'abc ')) But none of these seem to work as intended.
How can I remove these trailing ASCII 0 characters from the string?
I tried your code and there was no replacement so I check the ASCII code of your space and it was 32, not 0:
select replace('abc ', CHAR(32), '') + '*', ascii(right('abc ', 1)) Here I make for you your test data and replace char(0) using old sql server collation:
declare @t table (c varchar(128) collate latin1_general_ci_as); insert into @t values ('abc' + replicate(char(0),10)); select c, len(replace(c, char(0), '')) as len_not_replaced, replace(c collate SQL_Latin1_General_CP1_CI_AS, char(0), '') + '*' as correct_replacement from @t; 
LTRIM and RTRIM only remove spaces, not any whitespace character. If you are certain they are char(0) simply use replace.
declare @Something varchar(20) = 'abc' + replicate(char(0), 5) + 'def' select replace(@Something, char(0), '') 
REPLACE for CHAR(0) may not work, it depends on the column collation, as shown in the duplicate question. It will work if varchar is cast to varbinary first, or the collation is changed to a more suitable.
REPLACEshould work according to this script:DECLARE @a varchar(10) = CAST(0x616200 AS varchar(10)); SELECT DATALENGTH(@a) AS before, DATALENGTH(REPLACE(@a, CHAR(0), '')) AS after;