Removing Control Characters

• Graeme100

  SSCrazy Eights

  Points: 8054

  More actions

  November 30, 2004 at 3:57 am

  #66339

  Hi Guys,

  I have a field in a table that contains varchar information. Unfortunately the application that fills this field allows CRs and LFs to be stored with the text in the field.

  I would like to strip out the CHAR(10) and CHAR(13) and replace them with spaces (if necessary).

  I can find them within the string easily enough but I can't use the REPLACE command to change them to CHAR(32).

  Am I missing something ?

  Has anyone tried this before or got any ideas?

  Many Thanks....Graeme

   

• David.Poole

  SSC Guru

  Points: 76034

  More actions

  November 30, 2004 at 4:21 am

  #531829

  I've not had a problem with

  DECLARE @vbCRLF CHAR(2)

  SET @vbCRLF = CHAR(10) + CHAR(13)

  SELECT REPLACE(@myVarChar,@vbCRLF,' ')

  LinkedIn Profile

• Graeme100

  SSCrazy Eights

  Points: 8054

  More actions

  November 30, 2004 at 4:38 am

  #531831

  Hello,

  Yeah..that's pretty much what I tried but it does not seem to do anything.

  I check the string after I've run the Replace command using the following:

  SET TEXTSIZE 0

  DECLARE @position int, @string nvarchar(300)

  SET @position = 1

  SET @string = (select TEXT from TABLE where ID = 106)

  WHILE @position <= DATALENGTH(@string)

     BEGIN

     SELECT ASCII(SUBSTRING(@string, @position, 1)),

        CHAR(ASCII(SUBSTRING(@string, @position, 1)))

     SET @position = @position + 1

     END

  GO

  --FROM BOL

  ..and it still tells me I have a Char(10) and/or Char(13) within the string.

  Any thoughts?......Graeme

   

• Graeme100

  SSCrazy Eights

  Points: 8054

  More actions

  November 30, 2004 at 4:52 am

  #531833

  Ok,

  All sorted now...minor error in script

  Many Thanks

  Graeme

• David.Poole

  SSC Guru

  Points: 76034

  More actions

  November 30, 2004 at 4:52 am

  #531832

  I tried the following

  DECLARE @sText VARCHAR(250) ,

   @sRep VARCHAR(250)

  SELECT @sText = 'This is'+CHAR(10)+CHAR(13)+'is a'+CHAR(10)+CHAR(13)+'test' ,

   @sRep = REPLACE(@sText,CHAR(10)+CHAR(13),' ')

  PRINT @sText

  PRINT @sRep

  You've probably tried this already but try replacing just the CHAR(10) and confirm whether or not that works.

  Does the BOL code confirm that CHAR(10) and CHAR(13) are always together and in that order?

  Do you really need an NVARCHAR?  If so try using the NCHAR function instead of CHAR to see if that makes a difference.

  Just clutching at straws here.

  LinkedIn Profile

• Graeme100

  SSCrazy Eights

  Points: 8054

  More actions

  November 30, 2004 at 4:59 am

  #531834

  Thanks again for that David.

  For info the text could have either Char(10) or Char(13) or Both.

  I think our application needs correcting

  Cheers..Graeme