0

I have a sql server table where control characters appear when column is copied and pasted into notepad. I need to remove/replace these control characters. For example here is a text i copied from my sql server table into notepad

enter image description here

How do i remove "OSC". I have searched the net and here but cant find anything on this. Table was imported from SSIS as ANSI (i also tried data conversion in ssis to convert the column to ascii but still to no avail).

Hadi
  • 36,233
  • 13
  • 65
  • 124
Wsi
  • 111
  • 1
  • 9
  • Just FYI. OSC is Operating System Command. Please refer to this website for control characters list. https://www.aivosto.com/articles/control-characters.html – skadam85 Feb 10 '19 at 18:47

1 Answers1

1

"OSC" is CHAR(157). Try using REPLACE(Values, CHAR(157), ''). If it works then you can update in the table. Hope it helps.

skadam85
  • 483
  • 7
  • 19
  • 1
    That works. Is there a way to replace all such characters with its html version (other then replacing one a time)? e.g.replace(column, '¿','¿') – Wsi Feb 10 '19 at 19:01
  • If you are looking for dynamically replacing ascii control characters then you have to create a function. Check this link: https://www.sqlshack.com/replace-ascii-special-characters-sql-server/ – skadam85 Feb 10 '19 at 19:08
  • You might try `SELCET (SELECT YourStringWithSpecialCharacters AS [*] FOR XML PATH('')`. The XML engine will replace all special characters with XML entities. These entities should work with HTML as well... – Shnugo Feb 11 '19 at 07:42