My .02$ worth. Modify the table data. Otherwise you will be writing 3
queries each time you want any data from the table, one for the value, one
for the value with a space, one for value with 2 spaces. You might be able
to try the rtrim/ltrim functions but if there are any indexes on that column
they will not be used ( unless they are function based which depends on your
version and the previous DBA) and your performance will take a hit. This
is just my opinion, your mileage may vary.
John <jriker1_at_yahoo.com> wrote in message
news:e6bb5a8d.0201070548.6a75981b_at_posting.google.com...
> Scott,
>
> Thanks a lot. That really helped. I noticed a bunch of 20's at the
> end of each one. Now I need to decide whether to modify the table, or
> just adjust the query.
>
> JR
>
> "Scott Mattes" <ScottMattes_at_yahoo.com> wrote in message
news:<v24Z7.1289$gO5.886379_at_news1.news.adelphia.net>...
> > If it doesn't return anything when you query with a trailing space
either
> > there is more than one space or some non-displayable character.
> >
> > You might try doing a 'select rawtohex( field_name ) from yourtable' to
see
> > just exactly is there.
> >
> > If it is trailing spaces, you could probably remove them by updating the
> > table and setting the field equal to rtrim( ) of that field.
> >
> >
> > "John" <jriker1_at_yahoo.com> wrote in message
> > news:e6bb5a8d.0201031149.42e10df3_at_posting.google.com...
> > > I just inherited a database and to my dismay, my sql statements were
> > > failing to return results most of the time. I have a list of
> > > countries, and when I return a distinct on that list, placing " "
> > > characters before and after the data, there seems to be an extra space
> > > at the end of most of the countries. (Data visually returned: "UNITED
> > > STATES ". The field is a NOT NULL VARCHAR2(50) field. If I do a:
> > >
> > > country = 'UNITED STATES'
> > >
> > > it returns no results. If I do a:
> > >
> > > country like 'UNITED STATES%'
> > >
> > > it returns the US entry.
> > >
> > > If I just do a:
> > >
> > > country = 'UNITED STATES '
> > >
> > > with a space at the end, it fails to return anything.
> > >
> > > Any ideas what is going on here? A couple of the countries return
> > > with no extra blank looking spaces, and those work fine. Thanks.
> > >
> > > JR
Received on Mon Jan 07 2002 - 13:36:54 CST
