SQL REPLACE not working as expected

Question

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?

Ok you are updating the `temp_description` and selecting the `temp_definition` ... are you sure your updates aren't working? — Jacob H, Jun 16 '17 at 18:38
Are you sure they are spaces and not some other white space character? Like a tab maybe? — Sean Lange, Jun 16 '17 at 18:39
That's exactly what it was, the row result was so long that I had trouble figuring out that in some spaces it was a tab rather than a space. @SeanLange — vax, Jun 16 '17 at 18:48
[This](https://stackoverflow.com/a/35247507/92546) answer may help with future battles against whitespace. — HABO, Jun 16 '17 at 19:41

score 5 · Accepted Answer · answered Jun 16 '17 at 18:41

5

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

answered Jun 16 '17 at 18:41

SqlZim

37,248
6
41
59

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

1

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 16 '17 at 18:39

AXMIM

2,424
1
20
38

1

[`Len()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql) ignores trailing blanks, but _does_ count leading blanks. [`DataLength()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/datalength-transact-sql) does not ignore blanks, but returns the number of bytes, not characters. For Unicode strings the length may be obtained using `DataLength( @Foo ) / DataLength( N'x' )`. – HABO Jun 16 '17 at 19:37

SQL REPLACE not working as expected

2 Answers2

Edit :