Spaces in strings.. Check this out

• womalley

  Ten Centuries

  Points: 1329

  More actions

  September 10, 2003 at 11:37 am

  #82848

  What this does. Someone asked at another forum if there was a way to replace multiple spaces in a string with just one space. This works good. Please feel free to modify, change and add to this code.

  declare @r varchar(10)

  declare @LenWithSpaces INT

  declare @LenWithoutSpaces INT

  declare @NumOrSpacesToRemove INT

  SET @r = 'A B'

  SELECT @LenWithSpaces = LEN(@r)

  SELECT @LenWithoutSpaces = LEN(REPLACE(@r,' ',''))

  SELECT @NumOrSpacesToRemove = ((@LenWithSpaces - @LenWithoutSpaces)-1)

  SELECT @LenWithSpaces

  SELECT @LenWithoutSpaces

  SELECT @NumOrSpacesToRemove

  SELECT REPLACE(@r,SPACE(@NumOrSpacesToRemove),'')

  William O'Malley

• mccork

  SSC-Insane

  Points: 22288

  More actions

  September 10, 2003 at 3:42 pm

  #473557

  Yes, that will work if the string has just one set of multiple spaces. eg.

  SET @r = 'A  B'
  
  

  But it has no effect if there's 2 or more sets of multiple spaces:

  SET @r = 'A  B  C'
  
  

  Cheers,

  - Mark

  
  Cheers,
  - Mark

• 5409045121009-7368

  SSCoach

  Points: 15740

  More actions

  September 10, 2003 at 6:22 pm

  #473594

  
  
  Declare @r Varchar(100)
  
  Set @r='  a b  c   d     e  '
  
  While CharIndex('  ',@r)>0
  
  Set @r=Replace(@r,'  ',' ')
  
  Select @r
  
  

• womalley

  Ten Centuries

  Points: 1329

  More actions

  September 11, 2003 at 5:02 am

  #473666

  quote:

  ---

  
  
  Declare @r Varchar(100)
  
  Set @r='  a b  c   d     e  '
  
  While CharIndex('  ',@r)>0
  
  Set @r=Replace(@r,'  ',' ')
  
  Select @r
  
  

  ---

  Nice...

  Will