从 SQL Server 中的字符串中删除所有空格 [英] Remove all spaces from a string in SQL Server

问题描述

从 SQL Server 2008 中的字符串中删除所有空格的最佳方法是什么?

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

LTRIM(RTRIM(' a b ')) 将删除字符串左右两侧的所有空格，但我还需要删除中间的空格.

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.

推荐答案

直接替换即可；

SELECT REPLACE(fld_or_variable, ' ', '')

只是为了澄清；它是全局替换，无需 trim() 或担心 char 或 varchar 的多个空格:

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 

结果

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"

这篇关于从 SQL Server 中的字符串中删除所有空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！