How can I remove newline character from a string

• pradeep chandy

  Old Hand

  Points: 333

  More actions

  October 24, 2006 at 7:14 am

  #114891

  Hello,

  I might have a string which contains text, newline characters and spaces which is fine.

  And also, I might have a string which contains just newline characters or just spaces or both, which is not fine.

  I can remove spaces by using function TRIM(), but I am not sure how to remove newline characters from the string.

  I am thinking of using functions Pos() and Replace() in a loop to replace newline characters with nothing, but it is does not look like an altimate way to solve this problem.

  Is there any other simpler way to remove newline characters from a string or my choices are limited and I have to use Pos(0 and Replace() functions.

  Thank you

• Ram Ram

  Ten Centuries

  Points: 1079

  More actions

  October 24, 2006 at 7:19 am

  #667557

  Try this

  DECLARE @STR VARCHAR(20)

  SELECT @STR = 'HELLO'+CHAR(10)+'HELLO'+CHAR(13)+'DDD FFF'

  PRINT @STR

  PRINT REPLACE(REPLACE(@STR,CHAR(10),''),CHAR(13),'')

   

  Ram

   

• Aaron West

  SSC Eights!

  Points: 808

  More actions

  October 25, 2006 at 3:48 am

  #667797

  You may have defined the problem wrong. Did you mean to ask this, instead? :

  'How can I detect that a string has nothing but whitespace?'

  Replace space, newline and carriage-return characters with null string, and check if the result is a null string.

  SELECT @is_whitespace = CASE WHEN REPLACE(REPLACE(REPLACE(@str,' ',''),CHAR(10),''),CHAR(13),'') = '' THEN 1 ELSE 0 END

• kanchit.5.dhawan

  Valued Member

  Points: 51

  More actions

  December 6, 2007 at 12:23 am

  #757546

  Thanks Ram,

  Your suggestion worked for my query of removing newline characters.

  Thanks once again.

  Kanchit