我分享一个解决方案，在我看来，它非常快，但安装起来有点繁琐。它在Microsoft SQL Server 2008 R2 (SP3)上工作得很好:

在数据库所在的服务器上安装Sql Regex程序集。安装步骤如下: https://github.com/DevNambi/sql-server-regex#installation 在数据库中创建函数dbo。TRIM，它基于RegexReplace()标量函数(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

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

使用方法:

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"

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

更新:

要完全删除所有空格，您可以创建以下函数:

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

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

使用方法:

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"

简单地替换它;

SELECT REPLACE(fld_or_variable, ' ', '')

编辑: 澄清一下;它是一个全局替换，不需要trim()或担心char或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"

如果是对表的更新，则必须多次运行此更新，直到影响到0行为止。

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

一个功能版本(udf)，删除空格，cr, lf，制表符或可配置。

select Common.ufn_RemoveWhitespace(' 234   asdf   wefwef 3  x   ', default) as S

结果:234 asdfwefwef3x

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

替换特定字符的语法:

REPLACE ( string_expression , string_pattern , string_replacement )  

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

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

检查和尝试下面的脚本(单元测试)-

--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;