Need to Remove HEX Code A0 from Varchar Column

• GF

  Hall of Fame

  Points: 3774

  More actions

  April 28, 2008 at 11:47 am

  #71845

  Our application allows customers to upload large files of data to our SQL 2000 sp4 database.

  Recently some of the data is not displaying correctly. It appears as though there is white space before the text. When I researched it I found it was not a blank space but actually a space with a hex code of A0.

  I have tried to remove with the replace function but it does not work on this character.

  There are over 200,000 instances of the Hex Code A0 and I need to get rid of them.

  Can someone provide a script to help me remove these characters?

  Thanks

  Gary

• Absinthe-428914

  Mr or Mrs. 500

  Points: 514

  More actions

  April 28, 2008 at 12:08 pm

  #807974

  declare @junk varchar(20)

  declare @dejunk varchar(20)

  declare @clean varchar(20)

  set @junk = char(160) + char(160) + 'a' + char(160) + char(160) + 'b' + char(160) + char(160) + 'c'

  print @junk

  set @dejunk = replace(@junk,char(160),'*')

  print @dejunk

  set @clean = replace(@junk,char(160),'')

  print @clean