SQL Server 2008：如何查找尾随空格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2552407/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2008: How to find trailing spaces
提问by atricapilla
How can I find all column values in a column which have trailing spaces? For leading spaces it would simply be
如何在具有尾随空格的列中找到所有列值？对于领先的空间，它只是
select col from table where substring(col,1,1) = ' ';
回答by Jaxidian
You can find trailing spaces with LIKE:
您可以使用以下命令找到尾随空格LIKE：
SELECT col FROM tbl WHERE col LIKE '% '
回答by Neil Moss
SQL Server 2005:
SQL Server 2005：
select col from tbl where right(col, 1) = ' '
As a demo:
作为演示：
select
case when right('said Fred', 1) = ' ' then 1 else 0 end as NoTrail,
case when right('said Fred ', 1) = ' ' then 1 else 0 end as WithTrail
returns
回报
NoTrail WithTrail
0 1
回答by Velibor Dragutinovi?
This is what worked for me:
这对我有用：
select * from table_name where column_name not like RTRIM(column_name)
This will give you all the records that have trailing spaces.
这将为您提供所有带有尾随空格的记录。
If you want to get the records that have either leading or trailing spaces then you could use this:
如果您想获取具有前导或尾随空格的记录，则可以使用以下命令：
select * from table_name where column_name not like LTRIM(RTRIM(column_name))
回答by Allan S. Hansen
A very simple method is to use the LEN function. LEN will trim trailing spaces but not preceeding spaces, so if your LEN() is different from your LEN(REVERSE()) you'll get all rows with trailing spaces:
一个非常简单的方法是使用 LEN 函数。LEN 将修剪尾随空格但不会修剪前面的空格，因此如果您的 LEN() 与您的 LEN(REVERSE()) 不同，您将获得所有带有尾随空格的行：
select col from table where LEN(col) <> LEN(REVERSE(col));
this can also be used to figure out how many spaces you have for more advanced logic.
这也可以用来计算你有多少空间用于更高级的逻辑。
回答by Hubbitus
SELECT * FROM tbl WHERE LEN(col) != DATALENGTH(col)
Should work also.
也应该工作。
回答by SQL_Deadwood
There's a few different ways to do this...
有几种不同的方法可以做到这一点......
My favorite option, assuming your intention is to remove any leading and / or trailing spaces, is to execute the following, which will dynamically create the T-SQL to UPDATEall columns with an unwanted space to their trimmed value:
我最喜欢的选项，假设您的意图是删除任何前导和/或尾随空格，是执行以下操作，这将动态地为UPDATE所有列创建 T-SQL，并将不需要的空格设置为它们的修剪值：
SELECT
'UPDATE [<DatabaseName>].[dbo].['+TABLE_NAME+']
SET ['+COLUMN_NAME+']=LTRIM(RTRIM(['+COLUMN_NAME+']))
WHERE ['+COLUMN_NAME+']=LTRIM(RTRIM(['+COLUMN_NAME+']));'+CHAR(13)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE '<TableName>%'
AND DATA_TYPE!='date'
ORDER BY TABLE_NAME,COLUMN_NAME
If you really need to identify them though, try one of these queries:
如果您确实需要识别它们，请尝试以下查询之一：
SELECT *
FROM [database].[schema].[table]
WHERE [col1]!=LTRIM(RTRIM([col1]))
More dynamic SQL:
更动态的 SQL：
SELECT 'SELECT ''['+TABLE_NAME+'].['+COLUMN_NAME+']'',*
FROM [<your database name>].[dbo].['+TABLE_NAME+']
WHERE ['+COLUMN_NAME+'] LIKE ''% ''
OR ['+COLUMN_NAME+'] LIKE '' %'';
GO
'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE '<filter table name as desired>%'
AND DATA_TYPE!='date'
回答by besartm
Try this:
尝试这个：
UPDATE Battles
SET name = CASE WHEN (LEN(name+'a')-1)>LEN(RTRIM(name))
THEN REPLICATE(' ', (LEN(name+'a')-1)- LEN(RTRIM(name)))+RTRIM(name)
ELSE name
END
回答by gotqn
I have found the accepted answer a little bit slower:
我发现接受的答案有点慢：
SELECT col FROM tbl WHERE col LIKE '% ';
against this technique:
反对这种技术：
SELECT col FROM tbl WHERE ASCII(RIGHT([value], 1)) = 32;
The idea is to get the last char, but compare its ASCIIcode with the ASCIIcode of space instead only with ' '(space). If we use only ' 'space, an empty string will yield true:
这个想法是获取最后一个字符，但将其ASCII代码与ASCII空格代码进行比较，而不是仅与' '（空格）进行比较。如果我们只使用' '空格，则空字符串将产生 true：
DECLARE @EmptyString NVARCHAR(12) = '';
SELECT IIF(RIGHT(@EmptyString, 1) = ' ', 1, 0); -- this returns 1
The above is because of the Microsoft's implementation of string comparisons.
So, how fast exactly?
那么，究竟有多快？
You can try the following code:
您可以尝试以下代码：
CREATE TABLE #DataSource
(
[RowID] INT PRIMARY KEY IDENTITY(1,1)
,[value] NVARCHAR(1024)
);
INSERT INTO #DataSource ([value])
SELECT TOP (1000000) 'text ' + CAST(ROW_NUMBER() OVER(ORDER BY t1.number) AS VARCHAR(12))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
UPDATE #DataSource
SET [value] = [value] + ' '
WHERE [RowID] = 100000;
SELECT *
FROM #DataSource
WHERE ASCII(RIGHT([value], 1)) = 32;
SELECT *
FROM #DataSource
WHERE [value] LIKE '% ';
On my machine there is around 1 second difference:
在我的机器上有大约 1 秒的差异：
I have test it on table with 600k rows, but larger size, and the difference was above 8 seconds. So, how fast exactly will depend on your real case data.
我已经在 60 万行的表上对其进行了测试，但尺寸更大，差异超过 8 秒。因此，究竟有多快将取决于您的真实案例数据。
回答by Jonatan
Here's an alternative to find records with leading or trailing whitespace, including tabs etc:
这是查找带有前导或尾随空格的记录的替代方法，包括制表符等：
SELECT * FROM tbl WHERE NOT TRIM(col) = col
回答by Thunder
Spaces are ignored in SQL Server so for me even the leading space was not working .
SQL Server 中的空格被忽略，所以对我来说，即使是前导空格也不起作用。
select col from table where substring(col,1,1) = ' '
wont work if there is only one space (' ') or blank ('')
如果只有一个空格 (' ') 或空白 ('') 则不起作用
so I devised following:
所以我设计了以下内容：
select * from [table] where substring(REPLACE(col, ' ', '#'),1,1) = '#'
