Remove all spaces from a string in SQL Server

Question

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.

By "all white spaces" do you mean only regular spaces? Or, do you mean tabs, CR, LF, and other characters that may display as white space? — Gordon Linoff, May 03 '12 at 13:15

Alex K. · Accepted Answer · 2019-01-18T13:41:28.807

524

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"

edited Jan 18 '19 at 13:41

answered May 03 '12 at 13:11

Alex K.

171,639
30
264
288

2

What if there are multiple white spaces between words? REPLACE only removes one at a time. A user defined function should be written which removes multiple spaces. – Farhan Jul 20 '12 at 15:20
This doesn't seem to replace trailing white spaces – Ryan Sampson Apr 08 '13 at 21:37
7

It should replace all whitespace everywhere – Alex K. Apr 09 '13 at 10:01
It WILL remove all white spaces except for trailing. To remove trailing add TRIM(REPLACE(fld_or_variable, ' ', '')). The reason why it remove even multiple spaces is because... well it change every space characters to nothing wether the spaces are next to each other or not. – snaplemouton Apr 18 '13 at 19:30
37

Replace removes all spaces even trailing one. If there are still apprently spaces in the data after this is done, it is likely they are not spaces but unprintable characters like tabs or carraige returns. – HLGEM Apr 30 '13 at 15:15

score 51 · Answer 2 · answered May 03 '12 at 13:12

51

I would use a REPLACE

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

REPLACE

answered May 03 '12 at 13:12

Eduardo Crimi

1,551
13
13

SQL_King · Answer 3 · 2013-05-25T05:21:07.960

37

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 '%  %'

edited May 25 '13 at 05:21

answered May 25 '13 at 01:37

SQL_King

379
3
3

score 25 · Answer 4 · edited Feb 19 '18 at 07:05

25

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

edited Feb 19 '18 at 07:05

Rohit Agre

1,528
1
14
25

answered Jul 28 '16 at 12:53

Subroto Biswas

553
7
5

5

I had to use `select replace(replace(replace(varcharColumn,char(13),''),char(10),''),' ','')` to remove all newlines and spaces from a `varchar` column. If I used '\n' and '\r' instead of char(13) and char(10) it didn't work. – Jan May 02 '19 at 09:01
to execute this, it's needed to run "SET SQL_SAFE_UPDATES = 0;" – Shai Epstein Apr 18 '20 at 17:44
That was what I needed! Thank you!! – Rafael Grilli Aug 12 '21 at 20:33
Exactly what was required. Thanks! – alvinchesaro Jul 01 '22 at 15:59

score 19 · Answer 5 · answered May 03 '12 at 13:13

19

REPLACE() function:

REPLACE(field, ' ', '')

answered May 03 '12 at 13:13

Kaii

20,122
3
38
60

score 13 · Answer 6 · answered May 23 '16 at 19:08

Reference taken from this blog:

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

Do you realize the OP wanted to remove ALL spaces instead of replacing multiple spaces with a single one? — Kaii, Dec 20 '16 at 17:17
This should have been downvoted due to the answer is not as required by the OP, but instead it got 4 upvotes. Welcome to Stack Overflow. — Mr.J, Mar 13 '17 at 05:46

score 11 · Answer 7 · answered May 03 '12 at 13:12

11

t-sql replace http://msdn.microsoft.com/en-us/library/ms186862.aspx

replace(val, ' ', '')

answered May 03 '12 at 13:12

marko

10,684
17
71
92

score 11 · Answer 8 · edited Sep 21 '18 at 16:29

11

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

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

edited Sep 21 '18 at 16:29

Brien Foss

3,336
3
21
31

answered Aug 25 '17 at 17:36

Mario Rojas

136
1
7

score 9 · Answer 9 · answered Jul 20 '12 at 15:39

9

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

answered Jul 20 '12 at 15:39

Farhan

2,535
4
32
54

1

"@InputStr" should be "@ResultStr" in the body of the while loop. – jjoelson Mar 11 '13 at 17:53
@jjoelson would you mind to create this function and test it before pointing out mistakes? – Farhan Mar 12 '13 at 16:21
8

Sorry, I was working under your above assumption that REPLACE only removes one at a time, in which case this function would cause an infinite loop for inputs with multiple spaces. In reality, the while loop isn't even necessary at all. – jjoelson Mar 12 '13 at 19:55
3

Farhan, if REPLACE worked the way you think it does then @jjoelson's first comment is correct. Otherwise you'll have an infinite loop because InputStr never changes so ResultStr will always be the first REPLACE result. The reason your code works is because REPLACE is needed only 1 time. It's never called a second time no matter what string you throw at it. Add a counter and print it out with each iteration. It'll always be 1. REPLACE(InputStr, ' ', '') will remove every space with one call. – Gilbert Jul 31 '17 at 13:10

score 7 · Answer 10 · edited May 12 '22 at 15:51

7

replace(replace(column_Name,CHAR(13),''),CHAR(10),'')

edited May 12 '22 at 15:51

RF1991

2,037
4
8
17

answered Feb 14 '18 at 09:29

Abdullah Yousuf

403
5
7

score 5 · Answer 11 · answered Aug 12 '20 at 11:15

5

Try to use like this, if normal spaces are not removed by LTRM or RTRM

LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(Column_data, CHAR(9), ''), CHAR(10), ''), CHAR(13), '')))

answered Aug 12 '20 at 11:15

Bhaskara Arani

1,556
1
26
44

1

Had the same issue i.e. a whitespace at the start of the email string. I can confirm that this worked for me - thank you very much :) – GBM Feb 14 '23 at 21:27

score 3 · Answer 12 · answered Mar 07 '14 at 22:11

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

score 3 · Answer 13 · answered Nov 29 '17 at 21:50

To make all of the answers above complete, there are additional posts on StackOverflow on how to deal with ALL whitespace characters (see https://en.wikipedia.org/wiki/Whitespace_character for a full list of these characters):

score 3 · Answer 14 · answered Mar 08 '18 at 17:17

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

.

score 2 · Answer 15 · answered Mar 20 '15 at 20:47

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

score 2 · Answer 16 · answered Dec 20 '16 at 20:08

2

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).

answered Dec 20 '16 at 20:08

Agneum

727
7
23

score 1 · Answer 17 · answered Jul 19 '16 at 14:40

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'

score 1 · Answer 18 · answered Jan 18 '20 at 10:28

Check and Try the below script (Unit Tested)-

--Declaring
DECLARE @Tbl TABLE(col_1 VARCHAR(100));

--Test Samples
INSERT INTO @Tbl (col_1)
VALUES
('  EY     y            
Salem')
, ('  EY     P    ort       Chennai   ')
, ('  EY     Old           Park   ')
, ('  EY   ')
, ('  EY   ')
,(''),(null),('d                           
    f');

SELECT col_1 AS INPUT,
    LTRIM(RTRIM(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(col_1,CHAR(10),' ')
        ,CHAR(11),' ')
        ,CHAR(12),' ')
        ,CHAR(13),' ')
        ,CHAR(14),' ')
        ,CHAR(160),' ')
        ,CHAR(13)+CHAR(10),' ')
    ,CHAR(9),' ')
    ,' ',CHAR(17)+CHAR(18))
    ,CHAR(18)+CHAR(17),'')
    ,CHAR(17)+CHAR(18),' ')
    )) AS [OUTPUT]
FROM @Tbl;

score 0 · Answer 19 · edited Aug 13 '15 at 05:32

0

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"))

edited Aug 13 '15 at 05:32

Turnerj

4,258
5
35
52

answered Aug 13 '15 at 04:43

NarayanaReddy

301
2
4

score 0 · Answer 20 · answered Mar 08 '18 at 17:25

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

score 0 · Answer 21 · answered Jul 24 '18 at 23:32

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 (https://stackoverflow.com/a/4270085/538763)
        @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

score 0 · Answer 22 · answered Jan 26 '19 at 17:36

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)

Nick Allan · Answer 23 · 2021-10-29T11:07:53.337

I know the original question was about simply replacing spaces, but should you need to replace ALL whitespace, you can use the TRANSLATE function (since Sql Server 2019) to convert a given list of characters to something easier to replace. Then wrap it with the REPLACE function.

This saves repeated calls:

DECLARE @Whitespace CHAR(4) = CHAR(0) + CHAR(9) + CHAR(13) + CHAR(10);
SELECT REPLACE(
    TRANSLATE(' TEST    ', @Whitespace, '    '),
    ' ', '');

Checho Man · Answer 24 · 2022-09-24T22:25:50.337

I share a solution that -in my opinion- is very fast, but somewhat tedious to install. It works fine for me on Microsoft SQL Server 2008 R2 (SP3):

Install the Sql Regex assembly on the server where the database is installed. The installation steps are in the following link: https://github.com/DevNambi/sql-server-regex#installation
Create in the database the function dbo.TRIM, which is based on The RegexReplace() scalar function ( https://github.com/DevNambi/sql-server-regex#replace ):

CREATE FUNCTION [dbo].[TRIM](@text NVARCHAR(MAX))
  RETURNS NVARCHAR(MAX) --WITH SCHEMABINDING 
BEGIN

-- This function removes:
-- 1. invisible characters,
-- 2. repeated blank spaces and
-- 3. the spaces at the beginning and at the end of the text.

    RETURN (CASE
        WHEN @text is NULL 
            THEN NULL
        ELSE

            dbo.RegexReplace((dbo.RegexReplace(dbo.RegexReplace(@text, N'['+Nchar(0)+N'-'+Nchar(32)+Nchar(8192)+N'-'+Nchar(8202)+Nchar(160)+Nchar(5760)+Nchar(6158)+Nchar(8232)+Nchar(8233)+Nchar(8239)+Nchar(8287)+Nchar(65440)+Nchar(12288) +N']+', N'[}'), N'[\[\}]+', ' ')), N'^\s+|\s+$','')  

    END);
END
GO

....................

How to use:

declare @txt NVARCHAR(MAX) = N' Hello,'+Nchar(12288)+N'          my      '+NCHAR(160)+N'name'+Nchar(0)+N'     is John'+NCHAR(11)+N'            Doe';

select dbo.TRIM(@txt) as Result -- Result: "Hello, my name is John Doe"

....................

Update:

To completely remove all spaces, you can create the following function:

CREATE FUNCTION [dbo].[TRIM_SPACES](@text NVARCHAR(MAX))
  RETURNS NVARCHAR(MAX) --WITH SCHEMABINDING 
BEGIN
    RETURN (CASE WHEN @text is NULL THEN NULL ELSE
            dbo.RegexReplace(@text, N'['+Nchar(0)+N'-'+Nchar(32)+Nchar(8192)+N'-'+Nchar(8202)+Nchar(160)+Nchar(5760)+Nchar(6158)+Nchar(8232)+Nchar(8233)+Nchar(8239)+Nchar(8287)+Nchar(65440)+Nchar(12288) +N']+', N'')
    END)
END
GO

....................

How to use:

declare @txt NVARCHAR(MAX) = N' Hello,'+Nchar(12288)+N'          my      '+NCHAR(160)+N'name'+Nchar(0)+N'     is John'+NCHAR(11)+N'            Doe';

select dbo.TRIM_SPACES(@txt) as Result -- Result: "Hello,mynameisJohnDoe"

score -1 · Answer 25 · answered Aug 20 '19 at 17:36

It seems that everybody keeps referring to a single REPLACE function. Or even many calls of a REPLACE function. But when you have dynamic output with an unknown number of spaces, it wont work. Anybody that deals with this issue on a regular basis knows that REPLACE will only remove a single space, NOT ALL, as it should. And LTRIM and RTRIM seem to have the same issue. Leave it to Microsoft. Here's a sample output that uses a WHILE Loop to remove ALL CHAR(32) values (space).

DECLARE @INPUT_VAL  VARCHAR(8000)
DECLARE @OUTPUT_VAL VARCHAR(8000)

SET @INPUT_VAL = '      C               A                         '
SET @OUTPUT_VAL = @INPUT_VAL
WHILE CHARINDEX(CHAR(32), @OUTPUT_VAL) > 0 BEGIN
    SET @OUTPUT_VAL = REPLACE(@INPUT_VAL, CHAR(32), '')
END

PRINT 'START:' + @INPUT_VAL + ':END'
PRINT 'START:' + @OUTPUT_VAL + ':END'

Here's the output of the above code:

START:      C               A                         :END
START:CA:END

Now to take it a step further and utilize it in an UPDATE or SELECT statement, change it to a udf.

CREATE FUNCTION udf_RemoveSpaces (@INPUT_VAL    VARCHAR(8000))
RETURNS VARCHAR(8000)
AS 
BEGIN

DECLARE @OUTPUT_VAL VARCHAR(8000)
SET @OUTPUT_VAL = @INPUT_VAL
-- ITTERATE THROUGH STRING TO LOOK FOR THE ASCII VALUE OF SPACE (CHAR(32)) REPLACE IT WITH BLANK, NOT NULL
WHILE CHARINDEX(CHAR(32), @OUTPUT_VAL) > 0 BEGIN
    SET @OUTPUT_VAL = REPLACE(@INPUT_VAL, CHAR(32), '')
END

RETURN @OUTPUT_VAL
END

Then utilize the function in a SELECT or INSERT statement:

UPDATE A
SET STATUS_REASON_CODE = WHATEVER.dbo.udf_RemoveSpaces(STATUS_REASON_CODE)
FROM WHATEVER..ACCT_INFO A
WHERE A.SOMEVALUE = @SOMEVALUE

INSERT INTO SOMETABLE
(STATUS_REASON_CODE)
SELECT WHATEVER.dbo.udf_RemoveSpaces(STATUS_REASON_CODE)
FROM WHATEVER..ACCT_INFO A
WHERE A.SOMEVALUE = @SOMEVALUE

"REPLACE will only remove a single space." ...really? This simple demo suggests not: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=789d24b980741308871eba17722820c0 . Have you got an example of where that wouldn't work? — ADyson, Nov 11 '19 at 16:41

Remove all spaces from a string in SQL Server

25 Answers25

Linked

Related