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?
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).
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