I ran across a funny bug in SQLserver 2019, when trying to replace char(30) (ascii character 30) in a string, with an empty string (effectively removing the char30 from the string), the replace() function also strips all trailing spaces in the string. Please try the script below to see how it behaves:
SELECT @@version;
DECLARE @InputString varchar(100) = 'A B #' ;
WITH QueryInput AS
( SELECT @InputString as str1, replace(@InputString, '#', char(30)) as str2)
SELECT
str1 as str1, -- original string
len(str1) as str1_len, -- original string-length is 10
str2 as str2, -- replaced the last character with char(30)
len(str2) as str2_len, -- string length is still 10
replace(str2 , char(30), '_') as str3, -- replaced char(30) character with underscore
len( replace(str2 , char(30), '_')) as str3_len, -- string length is still 10
replace(replace(str2, ' ', '_'), char(30), '_') as str4, -- replaced spaces and then char(30) with underscore
len(replace(replace(str2, ' ', '_'), char(30), '_')) as str4_len, -- string length is still 10
replace(str2 , char(30), '' ) as str5, -- replaced char(30) with an empty string
len( replace(str2 , char(30), '' )) as str5_len -- string length drops to 3 (it removes the trailing spaces)
FROM QueryInput;
Here's the result I see:
