SQL Server 2019 replace() function does NOT replace char(30) with empty string properly

Benjamin Nunes 0 Reputation points
2025-04-04T17:43:27.09+00:00

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:

User's image

SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
177 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Viorel 121.4K Reputation points
    2025-04-04T18:00:42.1166667+00:00

    I think that the results are correct, because, for example, print len(' ') displays 0 and print len('a ') displays 1, i.e. the trailing spaces are not counted. To check the real length, try to select the value of datalength(replace(str2, char(30), '' )) as str6_len.

    You can also append some character to discover the invisible spaces.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.