Replace isn't working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Replace isn't working

Author

Topic

taunt
Posting Yak Master

128 Posts

Posted - 2014-01-13 : 13:14:28

Hello I'm trying to remove all the spaces in a sku field. Here's what I'm doing:

UPDATE    table
SET sku = REPLACE(LTRIM(RTRIM(sku)), ' ', '')

The problem is the replace works in the left, but on the right it leaves spaces. What might be the issue?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-01-13 : 13:21:06

What is the data type of sku?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-01-13 : 13:31:06

Also watch for special characters as they sometimes look like spaces.

djj

taunt
Posting Yak Master

128 Posts

Posted - 2014-01-13 : 13:37:29

quote:
Originally posted by djj55

Also watch for special characters as they sometimes look like spaces.

djj

nvarchar(10) and this is pasted right from sql LBD 170. I don't believe it's using other characters for space.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-01-13 : 13:47:42

What is "sql LBD 170"?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-13 : 14:21:59

quote:
Originally posted by taunt

quote:
Originally posted by djj55

Also watch for special characters as they sometimes look like spaces.

djj

nvarchar(10) and this is pasted right from sql LBD 170. I don't believe it's using other characters for space.

it may be non breaking space (char(160))

try this too


UPDATE    table
SET sku = REPLACE(REPLACE(LTRIM(RTRIM(sku)), ' ', ''),CHAR(160),'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

taunt
Posting Yak Master

128 Posts

Posted - 2014-01-13 : 14:34:28

This worked like a charm.

Thanks

quote:
Originally posted by visakh16

quote:
Originally posted by taunt

quote:
Originally posted by djj55

Also watch for special characters as they sometimes look like spaces.

djj

nvarchar(10) and this is pasted right from sql LBD 170. I don't believe it's using other characters for space.

it may be non breaking space (char(160))

try this too

UPDATE table
SET sku = REPLACE(REPLACE(LTRIM(RTRIM(sku)), ' ', ''),CHAR(160),'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-14 : 04:31:19

Cool..As I doubted it was a non breaking space which caused all the woes. I had same issue twice until I realized this.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources