We help IT Professionals succeed at work.

How to remove/replace spaces within a text string?

kgittinger
kgittinger used Ask the Experts™
on
How to remove/replace spaces within a text string?  The string can have 2 to 20 spaces seperating last name and the first name 2 to 5 spaces between the first name and the middle initial followed by spaces to fill the string to 31 spaces.  THe ultimate goal would be for force a comma after the last name.
Example:
"Doe                 Jane   A   "

Goal:
Doe, Jane A

Thank you very much
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This will eliminate the redundant spaces:



declare @s varchar(80)
select  @s = 'Doe          Jane A'

while CHARINDEX('  ', @s) > 0
  select @s = REPLACE(@s,'  ',' ')

select @s
Note - the first param in the "while" statement above contains 2 spaces.
-- This will also insert the comma:


declare @s varchar(80)
select  @s = 'Doe          Jane A'

while CHARINDEX('  ', @s) > 0      -- two spaces
  select @s = REPLACE(@s,'  ',' ')

-- select @s  

declare @i int
select  @i = CHARINDEX(' ',@s)

if @i > 0
  select @s = STUFF(@s,@i,1,', ')

select @s
Most Valuable Expert 2011
Top Expert 2012
Commented:
doing it all in sql

SELECT    left(name, charindex(' ', name) - 1)
       + ', '
       + left(LTRIM(substring(name, charindex(' ', name), 31)),
               charindex(' ', LTRIM(substring(name, charindex(' ', name), 31)))
              )
       + ' '
       + right(RTRIM(name), len(RTRIM(name)) - patindex('% [^ ]', RTRIM(name)))
from (select ltrim(rtrim('Doe                 Jane   A   ')) name) as x
-- here is a function to do the same:


create function udf_Format(@s varchar(8000))
  returns varchar(8000)
  WITH SCHEMABINDING
as
begin

  while CHARINDEX('  ', @s) > 0
    select @s = REPLACE(@s,'  ',' ')

  declare @i int
  select  @i = CHARINDEX(' ',@s)

  if @i > 0
    select @s = STUFF(@s,@i,1,', ')

  return @s
end
go

declare @s varchar(80)
select  @s = 'Doe          Jane A'

select @s = dbo.udf_Format(@s)
select @s as [result]

go
Anuradha GoliSystems Development / Support Specialist

Commented:
CREATE FUNCTION fnRemoveMultipleSpaces
               (@InputString VARCHAR(1024))
RETURNS VARCHAR(1024)
AS
  BEGIN
    WHILE CHARINDEX('  ',@InputString) > 0  -- Checking for double spaces
      SET @InputString =
        REPLACE(@InputString,'  ',' ') -- Replace 2 spaces with 1 space
    
    RETURN @InputString
  END
GO

Open in new window


Declare @data nvarchar(50)
Declare @SpacesRemoved nvarchar(50)
set @data = 'Doe                 Jane   A   '
SELECT 
       @SpacesRemoved = dbo.fnRemoveMultipleSpaces (@data)  
select	LTRIM(RTRIM(substring(@SpacesRemoved,0,charindex(' ',@SpacesRemoved))))+','+
		LTRIM(RTRIM(substring(@SpacesRemoved,charindex(' ',@SpacesRemoved)+1 ,len(@SpacesRemoved)))) as [Last Name]
 GO     

Open in new window

SQL Server DBA
Top Expert 2011
Commented:
Yes.. the SQL Way..


DECLARE @String VARCHAR(100)= 'Doe                 Jane   A   '

SELECT LEFT(@String,CHARINDEX(' ',@String)-1)+','
+LEFT(LTRIM(STUFF(@String,1,CHARINDEX(' ',@String),'')), CHARINDEX(' ',LTRIM(STUFF(@String,1,CHARINDEX(' ',@String),''))))
+RIGHT(@String, CHARINDEX(' ',@String)+1)