Remove all spaces from a string in SQL Server

I had this issue today and replace / trim did the trick..see below.



update table_foo
set column_bar = REPLACE(LTRIM(RTRIM(column_bar)), ' ', '')



before and after :

old-bad: column_bar | New-fixed: column_bar
' xyz ' | 'xyz'
' xyz ' | 'xyz'
' xyz ' | 'xyz'
' xyz ' | 'xyz'
' xyz ' | 'xyz'
' xyz ' | 'xyz'

Just a tip, in case you are having trouble with the replace function, you might have the datatype set to nchar (in which case it is a fixed length and it will not work).

To make all of the answers above complete, there are additional posts on StackOverflow on how to deal with ALL whitespace characters (see

[To see links please register here]

for a full list of these characters):

-

[To see links please register here]

-

[To see links please register here]

-

[To see links please register here]

100% working

UPDATE table_name SET "column_name"=replace("column_name", ' ', ''); //Remove white space

UPDATE table_name SET "column_name"=replace("column_name", '\n', ''); //Remove newline

UPDATE table_name SET "column_name"=replace("column_name", '\t', ''); //Remove all tab

You can use `"column_name"` or `column_name`

Thanks

Subroto

*this is useful for me:*

CREATE FUNCTION dbo.TRIM(@String VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String,CHAR(10),'[]'),CHAR(13),'[]'),char(9),'[]'),CHAR(32),'[]'),'][',''),'[]',CHAR(32))));
END
GO
.

**Syntax for replacing a specific characters:**

REPLACE ( string_expression , string_pattern , string_replacement )

For example in the string "HelloReplaceThingsGoing" Replace word is replaced by How

SELECT REPLACE('HelloReplaceThingsGoing','Replace','How');
GO

A functional version (udf) that removes spaces, cr, lf, tabs or configurable.

select Common.ufn_RemoveWhitespace(' 234 asdf wefwef 3 x ', default) as S

Result: '234asdfwefwef3x'


alter function Common.RemoveWhitespace
(
@pString nvarchar(max),
@pWhitespaceCharsOpt nvarchar(max) = null -- default: tab, lf, cr, space
)
returns nvarchar(max) as
/*--------------------------------------------------------------------------------------------------
Purpose: Compress whitespace

Example: select Common.ufn_RemoveWhitespace(' 234 asdf wefwef 3 x ', default) as s
-- Result: 234asdfwefwef3x

Modified By Description
---------- ----------- --------------------------------------------------------------------
2018.07.24 crokusek Initial Version
--------------------------------------------------------------------------------------------------*/
begin
declare
@maxLen bigint = 1073741823, -- (2^31 - 1) / 2 (

[To see links please register here]

)
@whitespaceChars nvarchar(30) = coalesce(
@pWhitespaceCharsOpt,
char(9) + char(10) + char(13) + char(32)); -- tab, lf, cr, space

declare
@whitespacePattern nvarchar(30) = '%[' + @whitespaceChars + ']%',
@nonWhitespacePattern nvarchar(30) = '%[^' + @whitespaceChars + ']%',
@previousString nvarchar(max) = '';

while (@pString != @previousString)
begin
set @previousString = @pString;

declare
@whiteIndex int = patindex(@whitespacePattern, @pString);

if (@whiteIndex > 0)
begin
declare
@whitespaceLength int = nullif(patindex(@nonWhitespacePattern, substring(@pString, @whiteIndex, @maxLen)), 0) - 1;

set @pString =
substring(@pString, 1, @whiteIndex - 1) +
iif(@whiteSpaceLength > 0, substring(@pString, @whiteIndex + @whiteSpaceLength, @maxLen), '');
end
end
return @pString;
end
go

This does the trick of removing the spaces on the strings:

UPDATE
tablename
SET
columnname = replace(columnname, ' ', '');

Simply replace it;

SELECT REPLACE(fld_or_variable, ' ', '')

**Edit:**
Just to clarify; its a global replace, there is no need to `trim()` or worry about multiple spaces for either `char` or `varchar`:

create table #t (
c char(8),
v varchar(8))

insert #t (c, v) values
('a a' , 'a a' ),
('a a ' , 'a a ' ),
(' a a' , ' a a' ),
(' a a ', ' a a ')

select
'"' + c + '"' [IN], '"' + replace(c, ' ', '') + '"' [OUT]
from #t
union all select
'"' + v + '"', '"' + replace(v, ' ', '') + '"'
from #t

**Result**

IN OUT
===================
"a a " "aa"
"a a " "aa"
" a a " "aa"
" a a " "aa"
"a a" "aa"
"a a " "aa"
" a a" "aa"
" a a " "aa"

For some reason, the replace works only with one string each time.
I had a string like this "**Test           MSP**" and I want to leave only one space.

I used the approach that @Farhan did, but with some modifications:


CREATE FUNCTION ReplaceAll
(
@OriginalString varchar(8000),
@StringToRemove varchar(20),
@StringToPutInPlace varchar(20)
)
RETURNS varchar(8000)
AS
BEGIN
declare @ResultStr varchar(8000)
set @ResultStr = @OriginalString
while charindex(@StringToRemove, @ResultStr) > 0
set @ResultStr = replace(@ResultStr, @StringToRemove, @StringToPutInPlace)

return @ResultStr
END

Then I run my update like this

UPDATE tbTest SET Description = dbo.ReplaceAll(Description, ' ', ' ') WHERE ID = 14225

Then I got this result:
**Test MSP**

Posting here if in case someone needs it as I did.

Running on:
Microsoft SQL Server 2016 (SP2)