Substituting white spaces, in a string, with nothing

• Mark Green-451799

  SSC-Addicted

  Points: 490

  More actions

  January 7, 2008 at 8:18 am

  #185349

  I can't find the answer to this question, although I know that I have asked it before. Appologies for repeating the question.

  I wish to substitue the white space in a string with nothing. So that a statement like :

  WHERE O.Postcode_no_spaces = D.Postcode_8

  reads:

  ...............WHERE O.Postcode_no_spaces = D.Postcode_8

  ....where D.Postcode_8 has had the white spaces removed.

• Todd Engen

  SSCrazy Eights

  Points: 9869

  More actions

  January 7, 2008 at 8:56 am

  #765391

  Would that be the REPLACE function you were thinking of?

  WHERE O.Postcode_no_spaces = REPLACE(D.Postcode_8, ' ', '')

• Loner

  SSC-Insane

  Points: 21279

  More actions

  January 7, 2008 at 1:28 pm

  #765496

  You can also do a RTRIM(LTRIM(.....)), this will eliminate any white space.

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  January 7, 2008 at 2:34 pm

  #765517

  Loner (1/7/2008)

  ---

  You can also do a RTRIM(LTRIM(.....)), this will eliminate any white space.

  RTRIM(LTRIM(.....)) will eliminate only leading and trailing spaces, not the ones in the middle of the string.

  _____________
  Code for TallyGenerator

• Tony Mungor

  SSC Eights!

  Points: 850

  More actions

  January 7, 2008 at 2:51 pm

  #765521

  Watch out for embedded carriage returns, tabs, etc...

  RTRIM(LTRIM()) and REPLACE(string,' ','') will not clean it all up.

  I don't have the full list at hand, mine is an Oracle version.

  Obvious ones to keep in mind are:

  REPLACE(string,char(10),'') -- Line Feed

  REPLACE(string,char(13),'') -- Carriage return

  REPLACE(string,char(9),'') -- Tab

  You can string them together like:

  select RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(MyStrColumn,char(13),''),char(10),''),char(9),''),' ',''))) as MyStrColumn

  from MyTable