SQL Server 2008: How to find trailing spaces

Question

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

Answer 1

You can find trailing spaces with LIKE:

SELECT col FROM tbl WHERE col LIKE '% '

Answer 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  

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

Answer 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:

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.

Answer 5

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

Should work also.

Answer 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:

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:

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'

Answer 7

Spaces are ignored in SQL Server so for me even the leading space was not working .

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

Answer 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

Answer 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

Answer 10

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 ASCII code with the ASCII code of space instead only with ' ' (space). If we use only ' ' space, an empty string will yield 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:

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.

Answer 11

Another way to achieve this by using CHARINDEX and REVERSE like below:

select col1 from table1
WHERE charindex(' ', reverse(col1)) = 1

See example Here

Answer 12

Simple use below query to get values having any number of spaces in begining or at end of values in column.

 select * from table_name where column_name like ' %' or column_name like '% ';

Answer 13

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

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

Answer 14

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 '_'