0

Using MS SQL 2016. The database Collation = SQL_Latin1_General_CP1_CI_AS.

Sometimes a string in a particular column [nvarchar(254)] contains one or more special characters, but sometimes those characters are no visible. When visible, they show like little boxes. See attached image. I found this: T-SQL Function to get ASCII values of characters stored. In this example, it tells me the character is a question mark, ascii code #63. I then use the code shown below to replace the question mark (and any other special characters) with an underscore but it won't do it. If the special character is visible, the code works. What am I doing wrong?

UPDATE TableName
set C_DESCRIPT=Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
Replace(C_DESCRIPT,char(60),'-'),char(62),'-'),char(58),'-'),char(34),'-'),char(47), '-'),
char(92),'-'),char(124),'-'), char(63),'-'), char(42),'-')
where CUSTOM_ALIAS='272968'

enter image description here

Anubix
  • 17
  • 4
  • That's likely a non-ascii character, which gets turned into a questionmark when trying to get the ASCII code because it doesn't have one – Erik A May 03 '23 at 22:07
  • So, there's no way to replace it with an underscore because I'll never know which character it is? – Anubix May 03 '23 at 22:12
  • https://stackoverflow.com/questions/106206/fastest-way-to-remove-non-numeric-characters-from-a-varchar-in-sql-server/74276585#74276585 – picobit May 03 '23 at 22:16
  • Nope, you should just get the unicode code instead and replace based on that (nchar instead of char). ASCII is extremely outdated – Erik A May 03 '23 at 22:16
  • How does the data get inserted and what is the column data type – Stu May 03 '23 at 22:25
  • 1
    If you want to replace “classes” of characters, rather than specific individual characters, then it’s much easier to use regex expressions rather than multiple levels of REPLACE. This post is one of many that gives examples of this: https://stackoverflow.com/questions/55821833/how-to-replace-non-ascii-characters-with-empty-values – NickW May 03 '23 at 22:42
  • Which dbms are you using? (Different products have different functions, especially when it comes to string handling.) – jarlh May 04 '23 at 06:26
  • @NickW, I will check out your suggestion right now! Thanks! – Anubix May 04 '23 at 14:20
  • @Stu, the data type is nvarchar(254) The data is being inserted into the table with data feed from another system. I understand, it would be best to clean the data coming in and we're working on that, but I'm also trying to clean data that has already been entered in the table. – Anubix May 04 '23 at 14:29

0 Answers0