在SQL Server 2008中删除字符串中的所有空格的最佳方法是什么?
LTRIM(RTRIM(' a b '))将删除字符串右侧和左侧的所有空格，但我还需要删除中间的空格。
简单地替换它;
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"
T-sql replace http://msdn.microsoft.com/en-us/library/ms186862.aspx
代表(val， '， '， ')
如果字符串中有多个空格，则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
例子:
select dbo.RemoveAllSpaces('aa aaa aa aa a')
输出:
aaaaaaaaaa
如果是对表的更新，则必须多次运行此更新，直到影响到0行为止。
update tableName
set colName = REPLACE(LTRIM(RTRIM(colName)), ' ', ' ')
where colName like '% %'
如果你需要在所有列中修剪空格，你可以使用这个脚本来动态地做它:
--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
如果你想从字符串中删除空格，-和另一个文本，那么使用以下命令:
假设您的表格中有一个手机号码，如“718-378-4957”或 ' 7183784957'，你想要替换并获得手机号码，然后使用下面的文本。
select replace(replace(replace(replace(MobileNo,'-',''),'(',''),')',''),' ','') from EmployeeContactNumber
结果:——7183784957
从左到右删除字符串中的空格。要消除中间空间，请使用Replace。
您可以使用RTRIM()从右边删除空格，使用LTRIM()从左边删除空格，因此左右空格被删除如下:
SELECT * FROM table WHERE LTRIM(RTRIM(username)) = LTRIM(RTRIM("Bob alias baby"))
参考本博客:
首先，创建示例表和数据:
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
脚本选择字符串没有额外的空格:
SELECT
[Rno]
,[Name] AS StringWithSpace
,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE([Name],CHAR(32),'()'),')(',''),'()',CHAR(32)))) AS StringWithoutSpace
FROM tbl_RemoveExtraSpaces
结果:
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
我今天遇到了这个问题，替换/修剪成功了，见下文。
update table_foo
set column_bar = REPLACE(LTRIM(RTRIM(column_bar)), ' ', '')
前后:
old-bad: column_bar | New-fixed: column_bar
' xyz ' | 'xyz'
' xyz ' | 'xyz'
' xyz ' | 'xyz'
' xyz ' | 'xyz'
' xyz ' | 'xyz'
' xyz ' | 'xyz'
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
可以使用“column_name”或“column_name”
谢谢
Subroto
为了使以上所有的答案完整，在StackOverflow上有关于如何处理所有空白字符的额外帖子(请参阅https://en.wikipedia.org/wiki/Whitespace_character获取这些字符的完整列表):
TSQL 2008使用LTrim(RTrim和仍然有空间的数据 如何从SQL server中的列中删除非间断空格? 在没有UDF和CLR的T-SQL中，从字符串中修剪所有空白字符的好方法是什么?
这对我来说很有用:
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
.
替换特定字符的语法:
REPLACE ( string_expression , string_pattern , string_replacement )
例如，在字符串“HelloReplaceThingsGoing”中，替换词被How替换
SELECT REPLACE('HelloReplaceThingsGoing','Replace','How');
GO
一个功能版本(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每次只对一个字符串起作用。 我有一个像这样的字符串“Test MSP”，我想只留下一个空格。
我使用了@Farhan的方法，但做了一些修改:
CREATE FUNCTION ReplaceAll
(
@OriginalString varchar(8000),
@StringToRemove varchar(20),
@StringToPutInPlace varchar(20)
)
RETURNS varchar(8000)
AS
BEGIN
declare @ResultStr varchar(8000)
set @ResultStr = @OriginalString
while charindex(@StringToRemove, @ResultStr) > 0
set @ResultStr = replace(@ResultStr, @StringToRemove, @StringToPutInPlace)
return @ResultStr
END
然后像这样运行更新
UPDATE tbTest SET Description = dbo.ReplaceAll(Description, ' ', ' ') WHERE ID = 14225
然后我得到了这个结果: 测试MSP
张贴在这里，如果有人需要它，就像我一样。
上运行: Microsoft SQL Server 2016 (SP2)
似乎每个人都在引用一个REPLACE函数。甚至多次调用REPLACE函数。但是当你有一个未知数量的空格的动态输出时，它就不起作用了。任何经常处理这个问题的人都知道REPLACE只会删除一个空格，而不是全部。LTRIM和RTRIM似乎有同样的问题。留给微软吧。 下面是一个示例输出，使用WHILE循环删除所有CHAR(32)值(空格)。
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:' + @INPUT_VAL + ':END'
PRINT 'START:' + @OUTPUT_VAL + ':END'
下面是上面代码的输出:
START: C A :END
START:CA:END
现在更进一步，在UPDATE或SELECT语句中使用它，将其更改为udf。
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
然后在SELECT或INSERT语句中使用该函数:
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
检查和尝试下面的脚本(单元测试)-
--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;
如果正规空格没有被LTRM或RTRM删除，请尝试这样使用
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(Column_data, CHAR(9), ''), CHAR(10), ''), CHAR(13), '')))
我知道最初的问题是关于简单地替换空格，但如果你需要替换所有空白，你可以使用TRANSLATE函数(自Sql Server 2019以来)将给定的字符列表转换为更容易替换的内容。然后用REPLACE函数包装它。
这样可以避免重复调用:
DECLARE @Whitespace CHAR(4) = CHAR(0) + CHAR(9) + CHAR(13) + CHAR(10);
SELECT REPLACE(
TRANSLATE(' TEST ', @Whitespace, ' '),
' ', '');
我分享一个解决方案，在我看来，它非常快，但安装起来有点繁琐。它在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"
推荐文章
- 确定记录是否存在的最快方法
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 外键约束可能导致循环或多条级联路径?
- 如何选择每一行的列值不是独特的
- nvarchar(max)非文本
- 在SQL Server 2008 R2中重命名数据库时出错
- 将数据复制到另一个表中
- 如何在SQL中选择表的最后一条记录?
- 修改列，添加默认约束
- 在存储过程中使用“SET XACT_ABORT ON”有什么好处?
- 如何检查SQL Server文本列是否为空?
- 如何创建一个SQL Server函数“连接”多行从一个子查询到一个单独的分隔字段?
