2

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?

Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
sfandler
  • 610
  • 5
  • 23
  • I believe a CHAR(0) is a NULL value. Have you tried CHAR(32)? Or convert to NVARCHAR(128) and then ltrim(rtrim('abc ')) – Messyeater Aug 24 '17 at 13:35
  • `REPLACE` should 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;` – Dan Guzman Aug 24 '17 at 13:38

2 Answers2

0

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), '')
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • Simple `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. – Vladimir Baranov Aug 24 '17 at 13:41
0

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;
sepupic
  • 8,409
  • 1
  • 9
  • 20
  • Yes, StackOverflow does not let me paste 0 ASCII characters into the textfield so I had to use regular spaces. – sfandler Aug 24 '17 at 14:09
  • 1
    Ok, in this case the question is duplicate and the answer is given already. I've just checked ascii just in case and wrote here what I've seen – sepupic Aug 24 '17 at 14:12
  • @sfandler I think a little and update my answer in case you still have your question uncleared – sepupic Aug 24 '17 at 14:25
  • That solves my problem. I already found the same solution somewhere else but thank you anyway. – sfandler Aug 24 '17 at 14:34