Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Varchar search is very literal

Re: Varchar search is very literal

From: Ethan Post <epost1_at_my-deja.com>
Date: Wed, 30 Aug 2000 20:56:02 GMT
Message-ID: <8ojsco$b3g$1@nnrp1.deja.com>

In article <8ojqfu$8k5$1_at_nnrp1.deja.com>,   jgotthelf_at_my-deja.com wrote:
> Hi,
>
> I have found that with a varchar2 field that searches seem to care
> about trailing spaces, i.e.:
>
> create table fred (fld1 varchar(10))
> insert into fred values ('cat ') -- 4 spaces after 'cat'
> commit
>
> select * from fred where fld1 = 'cat'
>
> returns zero rows. With regular 'Char' this works just fine.
>
> Is there a way to alter this behavior, or is this just the way it is?
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

This is the way it is and it makes sense as it is treating the spaces as literals where as with a char column the spaces are there like it or not. So when it can distiguish it does. My suggestion would be to get rid of the spaces if you can. Put a trigger on the column that trims the spaces off using rtrim() before the insert.

--
-Ethan
http://www.freetechnicaltraining.com
http://www.gnumetrics.com


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Wed Aug 30 2000 - 15:56:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US