How to remove an unknown special character?

• SQLDub

  SSC Veteran

  Points: 229

  More actions

  June 20, 2013 at 5:39 am

  #275450

  Hi,

  I am extracting data from a table to a .csv file in SSIS. The data in one of the fields has been copied from what looks like Microsoft Word and contains bullet points - some (not all) of these are causing the package to fail. I am unable to identify what sort of bullet point it is exactly as it loses any recognizable properties when copied, to replace it in the SQL query.

  Any ideas how I can select JUST the alpha-numeric text from the field excluding ALL special characters? (Instead of multiple REPLACE statements - even this won't work though as I don't know what the character is to map to its ASCII value).

  Thanks.

• crmitchell

  SSCarpal Tunnel

  Points: 4557

  More actions

  June 21, 2013 at 7:24 am

  #1626313

  Something like this - probably not particularly efficient but it should work.

  SET @MatchExpression= ''%[''+@MatchExpression+'']%''

  WHILE PATINDEX(@MatchExpression,@String)>0

  SET @String=STUFF(@String, PATINDEX(@MatchExpression, @String),1, '''')

  Where @MatchExpression is a regular expression to define your permitted characters.