isolate rows where varchar columns contain a trailing space

[Rich]

I need to isolate/retrieve rows from the following sample data where either
the login column or pwd column contains a trailing space ' ' (do not retrieve
rows where the column = '' blank). There are 3 rows that meet this criteria:
custIDs 111, 333, 888.

My issue is this -- I am currently querying for one column at a time and
using a Union All operator to combine the selected rows from each query into
one dataset, but I would prefer to do it in one shot. How do I write a tsql
query to retrieve the same rows without the Union All operator?

create table #tmp1(custID int, login varchar(5), pwd varchar(5))

insert into #tmp1
select 111, 'aaa','aaa'
union all select 111,'2aa','2aa '
union all select 222,'bbb','bbb'
union all select 333,'ccc ','ccc'
union all select 444,'ddd','ddd'
union all select 555,'eee','eee'
union all select 666,'fff',''
union all select 777,'ggg',''
union all select 888,'hhh','hhh '
union all select 999,'','iii'

select * from #tmp1
where right(pwd,1) = ' '
and datalength(pwd) > 0
union all
select * from #tmp1
where right(login,1) = ' '
and datalength(login) > 0

How can I perform this same operation without the Union All ?

Thanks,
Rich

[Tom Cooper]

select * from #tmp1
where (right(pwd,1) = ' ' and datalength(pwd) > 0)
Or (right(login,1) = ' ' and datalength(login) > 0);

Tom

"Rich" <Ri...@discussions.microsoft.com> wrote in message
news:72FF1EC0-902C-4916...@microsoft.com...

[Gregory A. Beamer]

=?Utf-8?B?UmljaA==?= <Ri...@discussions.microsoft.com> wrote in
news:72FF1EC0-902C-4916...@microsoft.com:

> I need to isolate/retrieve rows from the following sample data where
> either the login column or pwd column contains a trailing space ' '
> (do not retrieve rows where the column = '' blank). There are 3 rows
> that meet this criteria:
> custIDs 111, 333, 888.

So you want any with a ' ' at the end?

WHERE Trim(fieldName) <> FieldName

if these values are wrong, i would fix them and then put constraints (or
perhaps triggers) on the field so it cannot happen again. I am not fond of
data dumpsters where you can't trust the data stored in them.

Peace and Grace,
Greg

--
Vote for Miranda's Christmas Story
http://tinyurl.com/mirandabelieve

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

[Gregory A. Beamer]

=?Utf-8?B?UmljaA==?= <Ri...@discussions.microsoft.com> wrote in
news:72FF1EC0-902C-4916...@microsoft.com:

> I need to isolate/retrieve rows from the following sample data where

> either the login column or pwd column contains a trailing space ' '
> (do not retrieve rows where the column = '' blank). There are 3 rows
> that meet this criteria:
> custIDs 111, 333, 888.

let me correct my previous post a bit (been working too much Office
lately):

WHERE LTRIM(RTRIM(FieldName)) <> FieldName

In this case RTRIM may be enough.

[Rich]

Thanks all for your replies. I too have been working too much on stuff where
I can't think logically anymore. Anyway, the table gets populated by
customers from the web, and they inadvertently add spaces sometimes at the
end of their creds. I don't own this table - can't modify it. So I need to
identify the offenders and fixem at my end.

[Rich]

Note: I had to modify/Add

WHERE DataLength(RTRIM(FieldName)) <> DataLength(FieldName)

Now this works perfectly.

"Gregory A. Beamer" wrote:

> .
>

[Gert-Jan Strik]

Such complicated solutions for such a simple problem.

Try this:

SELECT *
FROM #tmp1
WHERE pwd LIKE '% '
OR login LIKE '% '

--
Gert-Jan
SQL Server MVP