从SQL Server中的字符串中删除所有空格

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

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

LTRIM(' a b ')将删除字符串右边和左边的所有空间，但我还需要删除中间的空间。

18 个解决方案

#1

253

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.

如果是对表的更新，您所要做的就是多次运行这个更新，直到它影响到0行。

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

#4

REPLACE() function:

取代()函数:

REPLACE(field, ' ', '')

#5

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

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

replace(val, ' ', '')

替换(val”、“,”)

#6

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

#7

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

如果字符串中有多个空格，则replace可能不能正确工作。为此，应该使用以下函数。

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

#8

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

这样就可以去掉字符串上的空格:

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

#9

100% working

100%的工作

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

您可以使用“column_name”或column_name

Thanks

谢谢

Subroto

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

假设您的表中有一个手机号码，比如“718-378-4957”或“7183784957”，您想要替换并获取手机号码，然后使用以下文本。

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

Result :-- 7183784957

结果:- 7183784957

#12

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'

#13

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

只是一个提示，如果您遇到替换函数的问题，您可能会将数据类型设置为nchar(在这种情况下，它是一个固定长度，它不会起作用)。

#14

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

为了完成以上所有的答案，在*上有关于如何处理所有空格字符的附加文章(这些字符的完整列表请参见https://en.wikipedia.org/wiki/Whitespace_character):

TSQL 2008 Using LTrim(RTrim and still have spaces in the data
TSQL 2008使用LTrim(RTrim)，数据中仍然有空格
How do I remove non-breaking spaces from a column in SQL server?
如何从SQL server中的列中删除不间断空格?
What's a good way to trim all whitespace characters from a string in T-SQL without UDF and without CLR?
在没有UDF和没有CLR的T-SQL字符串中，有什么好方法来减少所有空格字符?

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

您可以使用RTRIM()从右删除空格，而LTRIM()从左删除空格，因此左、右空格分别删除如下:

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

#16

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

替换(替换(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

例如在字符串“HelloReplaceThingsGoing”中，替换词是How

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

#1

253