CODEWITHSUNDEEP
sqlsql-servert-sql
vax
vax

Reputation: 60

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?

Upvotes: 4

Views: 7186

Answers (2)

AXMIM
AXMIM

Reputation: 2472

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

Upvotes: 1

SqlZim
SqlZim

Reputation: 38063

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

Upvotes: 8

Related Questions