SQL Server 2008:如何查找尾随空格

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) = ' ';

11 个解决方案

#1

SELECT col FROM tbl WHERE col LIKE '% '

从tbl中选择col如'% '

#2

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

#3

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

#4

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.

这也可以用来计算你有多少空间用于更高级的逻辑。

#5

SELECT * FROM tbl WHERE LEN(col) != DATALENGTH(col)

Should work also.

应该也工作。

#6

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 UPDATE all columns with an unwanted space to their trimmed value:

我最喜欢的一个选项，假设您的意图是删除任何前导和/或尾随空格，那么执行以下操作，它将动态创建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'

#7

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) = ' '

从substring(col,1,1) = '的表中选择col

wont work if there is only one space (' ') or blank ('')

如果只有一个空格(')或空格(")，则不能工作

so I devised following:

所以我设计了以下:

select * from table where substring(REPLACE(col, ' ', '#'),1,1) = '#'

从子字符串(替换(col， '， ' '， ' ')，1,1) = '#'的表中选择*

#8

Here is another alternative for trailing spaces.

这里还有另一种可选的尾随空格。

          DECLARE @VALUE VARCHAR(50) = NULL

          DECLARE @VALUE VARCHAR(50) = '  '

     IF ((@VALUE IS NOT NULL) AND  (LTRIM(RTRIM(@VALUE)) != ''))

     BEGIN
         SELECT 'TRUE'
     END
     ELSE
     BEGIN
         SELECT 'FALSE'
     END

#9

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

#10

-1

Here's an alternative to find records with leading or trailing whitespace, including tabs etc:

这里有一种替代方法，可以查找带有引导或尾随空格的记录，包括选项卡等:

SELECT * FROM tbl WHERE NOT TRIM(col) = col

#11

-1

We can try underscore to find the entries which are blanks,though not an accurate solution like using '% %' or ' ', But I could find entries which are blanks.

我们可以尝试下划线来查找空格，虽然不是像使用'% %'或' '这样的精确解决方案，但是我可以找到空白的条目。

select col_name from table where col_name like '_'

从表中选择col_name，其中col_name如'_'

#1