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

Solution 1

You can find trailing spaces with LIKE:

SELECT col FROM tbl WHERE col LIKE '% '

Solution 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  

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

Solution 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.

Solution 5

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

Should work also.

View more solutions

Comments

[Martin Smith]

There is no TRIM function in SQL Server and trailing spaces are ignored in = comparisons anyway.

[Jonatan]

Ok, sorry, I thought that was standard SQL. It works in PostgreSQL as expected though, and I found this question because i was trying to solve the same problem with PostgreSQL, so perhaps it can help someone else.

[10e5x]

I know this is a very old post, but i would like to know, if i would like to check if any columns in my table contain trailing spaces? Btw i got 45 fields in the table...how can i do so?

[Jaxidian]

@10e5x That is a different question. I recommend you post it as a different question. :-)

[Jaxidian]

@rodrigocprates: It depends. One option could be to create an additional BIT column to store a calculated value (trigger time?) of whether or not the field ends with a space. Another option would be to create a Computed Column to do the same thing. Or perhaps an indexed View to do the same thing. Lastly, something to try (it might be worse, or not) would be to use substrings to grab the last character and see if it is a space, although I suspect it would be slower.

[Monty Wild]

Only where col is not nvarchar.

[Hubbitus]

Good catch. According to sqlservercentral.com/Forums/Topic431183-8-1.aspx for nvarchar we should compare just to half of DATALENGTH value.

[SQL_Deadwood]

That will find all values which start with ANY character.

[SQL_Deadwood]

What do you mean by "spaces are ignored in SQL Server..."? Perhaps you mean in the context of the substring function?

[SQL_Deadwood]

In what way is it over the top?

[Fandango68]

SELECT col FROM tbl WHERE col LIKE '% '

[Hutch]

@SQL_Underworld Not sure what OP meant, but for an actual db value of 'test ', I had the row returned for all of 1. where test like 'test', 2. 'where test like 'test ', 3. where test = 'test' and 4. where test='test '. I also found that len(test) was 4 instead of 5. So it seems like all of len, like and = simply ignored the trailing space. The substring trick that Thunder suggests did replace the space with a #, so that len, like and = could be used. This is possibly encoding-specific.

[totymedli]

To remove it: UPDATE tablename SET columnname = RTRIM(columnname). See this answer.