Remove all spaces from a string in SQL Server

What is the best way to remove all spaces from a string in SQL Server 2008?

`LTRIM(RTRIM(' a b '))` would remove all spaces at the right and left of the string, but I also need to remove the space in the middle.

I would use a REPLACE

select REPLACE (' Hello , How Are You ?', ' ', '' )

[REPLACE][1]


[1]:

[To see links please register here]

t-sql replace

[To see links please register here]

replace(val, ' ', '')

[`REPLACE()` function](

[To see links please register here]

):

REPLACE(field, ' ', '')

If there are multiple white spaces in a string, then replace may not work correctly. For that, the following function should be used.


CREATE FUNCTION RemoveAllSpaces
(
@InputStr varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
declare @ResultStr varchar(8000)
set @ResultStr = @InputStr
while charindex(' ', @ResultStr) > 0
set @ResultStr = replace(@InputStr, ' ', '')

return @ResultStr
END

Example:

select dbo.RemoveAllSpaces('aa aaa aa aa a')

Output:

aaaaaaaaaa

If it is an update on a table all you have to do is run this update multiple times until it is affecting 0 rows.

update tableName
set colName = REPLACE(LTRIM(RTRIM(colName)), ' ', ' ')
where colName like '% %'

Just in case you need to TRIM spaces in all columns, you could use this script to do it dynamically:

--Just change table name
declare @MyTable varchar(100)
set @MyTable = 'MyTable'

--temp table to get column names and a row id
select column_name, ROW_NUMBER() OVER(ORDER BY column_name) as id into #tempcols from INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar', 'nvarchar') and TABLE_NAME = @MyTable

declare @tri int
select @tri = count(*) from #tempcols
declare @i int
select @i = 0
declare @trimmer nvarchar(max)
declare @comma varchar(1)
set @comma = ', '

--Build Update query
select @trimmer = 'UPDATE [dbo].[' + @MyTable + '] SET '

WHILE @i <= @tri
BEGIN

IF (@i = @tri)
BEGIN
set @comma = ''
END
SELECT @trimmer = @trimmer + CHAR(10)+ '[' + COLUMN_NAME + '] = LTRIM(RTRIM([' + COLUMN_NAME + ']))'+@comma
FROM #tempcols
where id = @i

select @i = @i+1
END

--execute the entire query
EXEC sp_executesql @trimmer

drop table #tempcols

if you want to remove spaces,-, and another text from string then use following :

suppose you have a mobile number in your Table like '718-378-4957' or
' 7183784957' and you want replace and get the mobile number then use following Text.

select replace(replace(replace(replace(MobileNo,'-',''),'(',''),')',''),' ','') from EmployeeContactNumber


Result :-- 7183784957

To remove the spaces in a string left and right. To remove space in middle use `Replace`.

You can use `RTRIM()` to remove spaces from the right and `LTRIM()` to remove spaces from the left hence left and right spaces removed as follows:

SELECT * FROM table WHERE LTRIM(RTRIM(username)) = LTRIM(RTRIM("Bob alias baby"))

**[Reference taken from this blog:][1]**


[1]:

[To see links please register here]

**First, Create sample table and data:**

CREATE TABLE tbl_RemoveExtraSpaces
(
Rno INT
,Name VARCHAR(100)
)
GO

INSERT INTO tbl_RemoveExtraSpaces VALUES (1,'I am Anvesh Patel')
INSERT INTO tbl_RemoveExtraSpaces VALUES (2,'Database Research and Development ')
INSERT INTO tbl_RemoveExtraSpaces VALUES (3,'Database Administrator ')
INSERT INTO tbl_RemoveExtraSpaces VALUES (4,'Learning BIGDATA and NOSQL ')
GO

**Script to SELECT string without Extra Spaces:**

SELECT
[Rno]
,[Name] AS StringWithSpace
,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE([Name],CHAR(32),'()'),')(',''),'()',CHAR(32)))) AS StringWithoutSpace
FROM tbl_RemoveExtraSpaces

**Result:**

Rno StringWithSpace StringWithoutSpace
----------- ----------------------------------------- ---------------------------------------------
1 I am Anvesh Patel I am Anvesh Patel
2 Database Research and Development Database Research and Development
3 Database Administrator Database Administrator
4 Learning BIGDATA and NOSQL Learning BIGDATA and NOSQL