Removing a pesky hidden character from a string

• Simon E Doubt

  SSCarpal Tunnel

  Points: 4191

  More actions

  November 30, 2007 at 8:40 am

  #179587

  Hello All:

  I am trying to remove a pesky hidden character from a string.

  The hidden character looks like a blank space, but when I copy/paste it into MsWord, and use the Show Paragraph/Formatting Marks option, the symbol it reveals looks something like the DEGREE symbol, i.e. a small circle. I've attached (I hope) a JPEG screencap of the symbol. It appears at the end of the sentence, between the period and the paragraph mark.

  I have tried using the following UPDATE code to get rid of it, where tblWeirdChar houses the string with the pesky char:

  UPDATE dbo.tblWeirdChar

  SET MyString = REPLACE(MyString,' ','')

  But that didn't work; I guess SQL Server knows that it's not a blank space.

  I also tried RTRIM, but that didn't work either.

  Can anyone advise?

  Thanks,

  Simon Doubt

  DBA, Fulcrum Publications, Inc.

• Simon E Doubt

  SSCarpal Tunnel

  Points: 4191

  More actions

  November 30, 2007 at 8:52 am

  #756003

  Just figured it out:

  I used:

  SELECT ASCII(RIGHT(MyString,1))

  FROM dbo.tblWeirdChar

  and the ASCII code was 160, which is a non-breaking space.

  So, I used:

  UPDATE dbo.tblWeirdChar

  SET MyString = REPLACE(MyString,CHAR(160),'')

  And it worked!

  Sorry for the self-posting.

  -Simon Doubt

  DBA, Fulcrum Publications, Inc.

• victorjcook

  SSC Journeyman

  Points: 79

  More actions

  December 21, 2012 at 3:38 pm

  #1571093

  THANKS THAT WAS JUST WHAT I NEEDED!

• Simon E Doubt

  SSCarpal Tunnel

  Points: 4191

  More actions

  December 21, 2012 at 4:06 pm

  #1571097

  victorjcook (12/21/2012)

  ---

  THANKS THAT WAS JUST WHAT I NEEDED!

  🙂

  Great - glad it was of some use.

  Happy Holidays!

  - SD