0

We're using SQL 2016

We have a process where we export data data from one system and import it into another.

I'm having some issues with the BEL and NAK ASCII characters and I'm looking to remove those from a string.

The initial text is stored in an ntext column. I had to cast the column to NVARCHAR to see the characters. I do not want to remove any alpha numeric characters, just the goofy ACSII characters.

I've used the function on this page, but it ended up removing all spaces and making data one long string. (https://www.sqlshack.com/replace-ascii-special-characters-sql-server/). I'm guessing because the string length can be very large (over 4000)

Another thing I've done is used the replace function to strip out the hex (x07, x0D) from the string. It works on some of these, but not all. Here's an example of one with both characters in it.

Quarters 1-4    Q1 - January 1- March 31    Q2 - April 1 - June 30    Q3 - July 1 - September 30    Q4 - October 1 - December 31

How would I strip those out?

jdids
  • 561
  • 1
  • 7
  • 22
  • https://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server – BI Dude Nov 20 '19 at 14:49
  • Possible duplicate of [How to strip all non-alphabetic characters from string in SQL Server?](https://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server) – BI Dude Nov 20 '19 at 14:50
  • This doesn't work as I need alpha numeric, plus it ends up removing spaces too. I just need the goofy ASCII characters removed – jdids Nov 20 '19 at 16:14

1 Answers1

1

Did you try the standard replace?

select replace(replace(@s, char(7), ''), char(21), '')
sepupic
  • 8,409
  • 1
  • 9
  • 20