SQL REPLACE not working as expected

I have a temp table that I’m trying to eliminate all the white spaces from a specific column. However my replace isn’t working at all. Here’s the code I have

IF OBJECT_ID('tempdb..#attempt1temptable') IS NOT NULL 
BEGIN
    DROP TABLE #attempt1temptable
END
GO 

CREATE TABLE #attempt1temptable
(
   temp_description            varchar(MAX),
   temp_definition             varchar(MAX)
)

INSERT INTO #attempt1temptable
 SELECT  graphic_description, graphic_definition
 FROM graphic

UPDATE #attempt1temptable SET  temp_description=REPLACE(temp_description, ' ', '')
UPDATE #attempt1temptable SET  temp_description=REPLACE(temp_description,  char(160), '')

--I have no idea why it won't update correctly here

select temp_description, LEN(temp_description) from #attempt1temptable

The Insert and select work as expected however it’s not updating temp_description to have no white spaces. The result of the query gives me the temp_description without anything changed to it. What am I doing wrong here?

  • Format datetime column into DD MONTH YYYY
  • Using CROSS APPLY
  • Calculate business hours between two dates
  • Needing to get a list of strings into a webservice
  • How to create Temp table with SELECT * INTO tempTable FROM CTE Query
  • Update listbox on time asp.net
  • 2 Solutions collect form web for “SQL REPLACE not working as expected”

    Try replacing some other whitespace characters:

    select replace(replace(replace(replace(
       description
      ,char(9)/*tab*/,'')
      ,char(10)/*newline*/,'')
      ,char(13)/*carriage return*/,'')
      ,char(32)/*space*/,'')
    from #attemp1temptable
    

    You are probably dealing with other characters than space. You could be dealing with tab for example.
    I would suggest to copy and paste the character to remove from the actual data into your replace statement to ensure you have the right character(s).

    Edit :

    Also, you seem to use LEN to verify if the data was updated or not. However, keep in mind that LEN doesn’t count trailing white space as character. So the count might not change even if the data was updated

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.