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.

回答1:

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:

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 

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'

回答2:

I would use a REPLACE

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

REPLACE

回答3:

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

回答4:

REPLACE() function:

REPLACE(field, ' ', '')

回答5:

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

回答6:

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

replace(val, ' ', '')

回答7:

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

回答8:

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

回答9:

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

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

回答10:

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

回答11:

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

回答12:

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

回答13:

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

• TSQL 2008 Using LTrim(RTrim and still have spaces in the data
• How do I remove non-breaking spaces from a column in SQL server?
• What's a good way to trim all whitespace characters from a string in T-SQL without UDF and without CLR?

回答14:

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' 

回答15:

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

回答16:

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

回答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

.

回答18:

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

回答19:

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