Remove "enter" character ¶ and update it...

• KGC

  Old Hand

  Points: 374

  More actions

  May 5, 2005 at 9:41 am

  #102514

  How can I remove the "enter" character from the contents of an SQL Server Tables and replace it with a space character or with a "-" character?

  for example I have the following table contents:

  "Test1 Desc 

  Test2 Desc ?.?. 8162100"

  and i want to update it with:

  "Test1 Desc - Test2 Desc ?.?. 8162100"

  Best regards,

  kchrist

   

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         

   

• Nicholas Cain

  SSC-Dedicated

  Points: 33009

  More actions

  May 5, 2005 at 9:55 am

  #557126

  I use a proc to do this, it replaces the carriage return and the linefeed (either or both could be in the string)

   

  CREATE PROCEDURE USP_REMOVE_CRLF @INSTRING VARCHAR(4000)

  AS

  SELECT @INSTRING = REPLACE(@INSTRING, CHAR(10), ' ')

  SELECT @INSTRING = REPLACE(@INSTRING, CHAR(13), ' ')

  SELECT @INSTRING

   

  or as an update

  UPDATE TABLEA

  SET COLUMNB = REPLACE(REPLACE(COLUMNB, CHAR(10), ' '), CHAR(13), ' ')

  ---

  
  Shamless self promotion - read my blog http://sirsql.net

• KGC

  Old Hand

  Points: 374

  More actions

  May 5, 2005 at 10:15 am

  #557142

  Thank you very very much Nicholas !!!