Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Varchar search is very literal
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