Replace bad characters

• Barcelona10

  SSCertifiable

  Points: 5372

  More actions

  August 25, 2023 at 6:23 pm

  #4271348

  Hi all

  I need help to replace bad characters with space  in string.

  select col1,

  patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,col1) as [Position],

  substring(col1,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,col1),1) as [InvalidCharacter],

  ascii(substring(col1,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,col1),1)) as [ASCIICode]

  from myTable

  where patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,col1) >0

  Here is the result:

  Col1                                                                         Position                  InvalidCharacter                    ASCIICode

  Don’t park in red zone                                              4                                     ’                                               146

  FLDNG SEAT’D WLKR & REG CANE                 11                                     ’                                               146

  Please approach… thanks!                                      16                                   …                                               133

  .......

  Thank You advance!

   

• ratbak

  Ten Centuries

  Points: 1358

  More actions

  August 25, 2023 at 7:54 pm

  #4271394

  replace(replace(col1,char(146),''),char(133),'')