blocks|key|580389|text|我会用替身|type|unstyled|depth|inlineStyleRanges|entityRanges|data|580390|select+REPLACE+('+Hello+,+How+Are+You+?',+'+',+''+)|code-block|syntax|javascript|580391|REPLACE|offset|length|580392|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms186862.aspx^0|0|0|0|7|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

I would use a REPLACE

<pre><code>select REPLACE (' Hello , How Are You ?', ' ', '' )
</code></pre>

<a href="http://msdn.microsoft.com/en-us/library/ms186862.aspx">REPLACE</a>

blocks|key|364576|text|t-sql替换http://msdn.microsoft.com/en-us/library/ms186862.aspx|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|364577|替换(val，‘'，'')|364578|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms186862.aspx^0|7|1H|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

t-sql replace <a href="http://msdn.microsoft.com/en-us/library/ms186862.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/ms186862.aspx</a>

replace(val, ' ', '')

blocks|key|364591|text|REPLACE()+function|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|364592|REPLACE(field,+'+',+'')|code-block|syntax|javascript|364593|entityMap|0|LINK|mutability|MUTABLE|url|http://technet.microsoft.com/en-us/library/ms186862.aspx^0|0|9|0|I|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

<a href="http://technet.microsoft.com/en-us/library/ms186862.aspx" rel="noreferrer"><code>REPLACE()</code> function</a>:

<pre><code>REPLACE(field, ' ', '')
</code></pre>

blocks|key|580500|text|如果字符串中有多个空格，则替换可能无法正常工作。为此，应使用以下函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|580501|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|code-block|syntax|javascript|580502|示例：|580503|select+dbo.RemoveAllSpaces('aa++aaa+++++++aa+aa+++++++++++++++++a')|580504|输出：|580505|aaaaaaaaaa|580506|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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

<pre><code>CREATE FUNCTION RemoveAllSpaces
(
 @InputStr varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
declare @ResultStr varchar(8000)
set @ResultStr = @InputStr
while charindex(' ', @ResultStr) &gt; 0
 set @ResultStr = replace(@InputStr, ' ', '')

return @ResultStr
END
</code></pre>

Example:

<pre><code>select dbo.RemoveAllSpaces('aa aaa aa aa a')
</code></pre>

Output:

<pre><code>aaaaaaaaaa
</code></pre>

blocks|key|364679|text|如果是对表进行更新，那么您所要做的就是多次运行此更新，直到它影响到0行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|364680|update+tableName
set+colName+=+REPLACE(LTRIM(RTRIM(colName)),+'++',+'+')
where+colName+like+'%25++%25'|code-block|syntax|javascript|364681|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>update tableName
set colName = REPLACE(LTRIM(RTRIM(colName)), ' ', ' ')
where colName like '% %'
</code></pre>

blocks|key|364709|text|以防您需要裁剪所有列中的空格，您可以使用此脚本动态执行此操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|364710|--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].['+%2B+@MyTable+%2B+']+SET+'

WHILE+@i+<=+@tri+
BEGIN

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

++++select+@i+=+@i%2B1
END

--execute+the+entire+query
EXEC+sp_executesql+@trimmer

drop+table+#tempcols|code-block|syntax|javascript|364711|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>--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 &lt;= @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
</code></pre>

blocks|key|364758|text|如果要从字符串中删除空格、-和其他文本，请使用以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|364759|假设您的表中有一个手机号码，如'718-378-4957‘或’7183784957‘，您希望替换并获取手机号码，然后使用以下文本。|364760|select+replace(replace(replace(replace(MobileNo,'-',''),'(',''),')',''),'+','')+from+EmployeeContactNumber|code-block|syntax|javascript|364761|结果:--+7183784957|364762|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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.

<pre><code>select replace(replace(replace(replace(MobileNo,'-',''),'(',''),')',''),' ','') from EmployeeContactNumber
</code></pre>

Result :-- 7183784957

blocks|key|580681|text|删除字符串中的左右空格。要删除中间的空格，请使用Replace。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|580682|您可以使用RTRIM()删除右侧的空格，使用LTRIM()删除左侧的空格，因此可以删除左侧和右侧的空格，如下所示：|580683|SELECT+*+FROM+table+WHERE+LTRIM(RTRIM(username))+=+LTRIM(RTRIM("Bob+alias+baby"))|code-block|syntax|javascript|580684|entityMap^0|O|7|0|5|7|M|7|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@$9|S|A|T|B|C]|$9|U|A|V|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|W|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|N|$]]

To remove the spaces in a string left and right. To remove space in middle use <code>Replace</code>.

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

<pre><code>SELECT * FROM table WHERE LTRIM(RTRIM(username)) = LTRIM(RTRIM("Bob alias baby"))
</code></pre>

blocks|key|364852|text|我今天遇到了这个问题，并替换/修剪了下面的trick..see。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|364853|update+table_foo+
set+column_bar++=+REPLACE(LTRIM(RTRIM(column_bar)),+'++',+'')|code-block|syntax|javascript|364854|之前和之后：|364855|old-bad:++column_bar++++%7C+++New-fixed:+++column_bar
+++++++'++xyz++'++++++++%7C++++++++++++++++'xyz'+++
+++++++'++xyz++'++++++++%7C++++++++++++++++'xyz'+
+++++++'++xyz++'++++++++%7C++++++++++++++++'xyz'+
+++++++'++xyz++'++++++++%7C++++++++++++++++'xyz'+
+++++++'++xyz++'++++++++%7C++++++++++++++++'xyz'+
+++++++'++xyz++'++++++++%7C++++++++++++++++'xyz'+|364856|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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

<pre><code>update table_foo 
set column_bar = REPLACE(LTRIM(RTRIM(column_bar)), ' ', '')
</code></pre>

before and after : 

<pre><code>old-bad: column_bar | New-fixed: column_bar
 ' xyz ' | 'xyz' 
 ' xyz ' | 'xyz' 
 ' xyz ' | 'xyz' 
 ' xyz ' | 'xyz' 
 ' xyz ' | 'xyz' 
 ' xyz ' | 'xyz' 
</code></pre>

blocks|key|580729|text|100%25工作|type|unstyled|depth|inlineStyleRanges|entityRanges|data|580730|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|code-block|syntax|javascript|580731|您可以使用"column_name"或column_name|offset|length|style|CODE|580732|谢谢|580733|Subroto|580734|entityMap^0|0|0|5|D|J|B|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@$I|V|J|W|K|L]|$I|X|J|Y|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

100% working

<pre><code>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
</code></pre>

You can use <code>"column_name"</code> or <code>column_name</code>

Thanks

Subroto

blocks|key|580775|text|这只是一个提示，以防您在使用replace函数时遇到问题，您可以将数据类型设置为nchar+(在这种情况下，它是固定的长度，它将不起作用)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|580776|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|364964|text|这样做可以删除字符串上的空格：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|364965|UPDATE
++++tablename
SET
++++columnname+=+replace(columnname,+'+',+'');|code-block|syntax|javascript|364966|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>UPDATE
 tablename
SET
 columnname = replace(columnname, ' ', '');
</code></pre>

blocks|key|365015|text|替换(replace(column_Name，CHAR(13)，'')，CHAR(10)，'')|type|unstyled|depth|inlineStyleRanges|entityRanges|data|365016|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|580907|text|删除空格、cr、lf、制表符或可配置的功能版本(udf)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|580908|select+Common.ufn_RemoveWhitespace('+234+++asdf+++wefwef+3++x+++',+default)+as+S|code-block|syntax|javascript|580909|结果：'234asdfwefwef3x‘|580910|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%5E31+-+1)+/+2+(https://stackoverflow.com/a/4270085/538763)
++++++++@whitespaceChars+nvarchar(30)+=+coalesce(
++++++++++++@pWhitespaceCharsOpt,+
++++++++++++char(9)+%2B+char(10)+%2B+char(13)+%2B+char(32));++--+tab,+lf,+cr,+space

++++declare
++++++++@whitespacePattern+nvarchar(30)+=+'%25['+%2B+@whitespaceChars+%2B+']%25',
++++++++@nonWhitespacePattern+nvarchar(30)+=+'%25[%5E'+%2B+@whitespaceChars+%2B+']%25',
++++++++@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)+%2B+
++++++++++++++++iif(@whiteSpaceLength+>+0,+substring(@pString,+@whiteIndex+%2B+@whiteSpaceLength,+@maxLen),+'');
++++++++end++++++++
++++end++++++++
++++return+@pString;
end
go|580911|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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

<pre><code>select Common.ufn_RemoveWhitespace(' 234 asdf wefwef 3 x ', default) as S
</code></pre>

Result: '234asdfwefwef3x'

<pre><code>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 &gt; 0)
 begin 
 declare 
 @whitespaceLength int = nullif(patindex(@nonWhitespacePattern, substring(@pString, @whiteIndex, @maxLen)), 0) - 1; 

 set @pString = 
 substring(@pString, 1, @whiteIndex - 1) + 
 iif(@whiteSpaceLength &gt; 0, substring(@pString, @whiteIndex + @whiteSpaceLength, @maxLen), '');
 end 
 end 
 return @pString;
end
go
</code></pre>

blocks|key|580981|text|似乎每个人都在引用一个单独的替换函数。或者甚至多次调用替换函数。但是当你的动态输出包含未知数量的空格时，它就不能工作了。任何经常处理这个问题的人都知道，替换只会删除一个空格，而不是所有空格。LTRIM和RTRIM似乎也有同样的问题。把它留给微软吧。下面是使用WHILE循环删除所有CHAR(32)值(空格)的示例输出。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|580982|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:'+%2B+@INPUT_VAL+%2B+':END'
PRINT+'START:'+%2B+@OUTPUT_VAL+%2B+':END'|code-block|syntax|javascript|580983|下面是上面代码的输出：|580984|START:++++++C+++++++++++++++A+++++++++++++++++++++++++:END
START:CA:END|580985|现在，要更进一步并在UPDATE或SELECT语句中使用它，请将其更改为udf。|580986|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|580987|然后在SELECT或INSERT语句中使用函数：|580988|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|580989|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

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

<pre><code>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) &gt; 0 BEGIN
 SET @OUTPUT_VAL = REPLACE(@INPUT_VAL, CHAR(32), '')
END

PRINT 'START:' + @INPUT_VAL + ':END'
PRINT 'START:' + @OUTPUT_VAL + ':END'
</code></pre>

Here's the output of the above code:

<pre><code>START: C A :END
START:CA:END
</code></pre>

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

<pre><code>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) &gt; 0 BEGIN
 SET @OUTPUT_VAL = REPLACE(@INPUT_VAL, CHAR(32), '')
END

RETURN @OUTPUT_VAL
END
</code></pre>

Then utilize the function in a SELECT or INSERT statement:

<pre><code>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
</code></pre>

blocks|key|581015|text|检查并尝试以下脚本(单元测试)-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|581016|--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)%2BCHAR(10),'+')
++++,CHAR(9),'+')
++++,'+',CHAR(17)%2BCHAR(18))
++++,CHAR(18)%2BCHAR(17),'')
++++,CHAR(17)%2BCHAR(18),'+')
++++))+AS+[OUTPUT]
FROM+@Tbl;|code-block|syntax|javascript|581017|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Check and Try the below script (Unit Tested)-

<pre><code>--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;
</code></pre>

blocks|key|365199|text|如果LTRM或RTRM没有删除正常的空格，请尝试这样使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|365200|LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(Column_data,+CHAR(9),+''),+CHAR(10),+''),+CHAR(13),+'')))|code-block|syntax|javascript|365201|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try to use like this, if normal spaces are not removed by LTRM or RTRM
<pre><code>LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(Column_data, CHAR(9), ''), CHAR(10), ''), CHAR(13), '')))
</code></pre>

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

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

Remove all spaces from a string in SQL Server

单元测试

SQL Server

在SQL Server 2008中，删除字符串中所有空格的最佳方法是什么？LTRIM(RTRIM('  a b '))将删除字符串右侧和左侧的所有空格，但我还需要删除中间的空格。

问在SQL Server中删除字符串中的所有空格
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL Server中删除字符串中的所有空格EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL Server中删除字符串中的所有空格
EN