LIKE command with NUMERIC field?

Thread View: borland.public.interbase.sql

3 messages

[T] Back to Threads [V] Tree View [G] Group Articles

3 total messages Started by Kevin Naidoo Wed, 27 Nov 2002 12:20

#11308

Author: Kevin Naidoo
Date: Wed, 27 Nov 2002 12:20

20 lines
1631 bytes

Hi there,

A few questions about LIKE and NUMERIC fields :-

1) We currently use LIKE with some of our NUMERIC fields. I know this is not
how it should be used, so can anyone give a method to do such comparisons
with NUMERIC fields? I need to be able to find NUMERICs starting with say 10
(like 10, 101, 10201 etc.).

2) This used to work fine with IB 5.6. I've just upgraded to IB 6.5 and now
the problem seems to be that I need to include a space in front of the value
I'm looking for. eg. LIKE ' 10%' (LIKE '10%' worked fine in IB5.6). Negative
values work fine eg. LIKE '-10%'. Any ideas about this? (I tried a LIKE
'+10%' to no avail.)

Hope my questions make sense.

Regards,
Kevin Naidoo
M.Ying and Associates

Re: LIKE command with NUMERIC field?

#11310

Author: Martijn Tonies
Date: Wed, 27 Nov 2002 12:55

48 lines
2263 bytes

Hi,

What if you CAST it to VARCHAR first?

WHERE CAST(mynumericfield AS VARCHAR(10)) LIKE '10%'


Actually, upon testing, it works fine with a database I have... ?!

select * from BESTELLING
where bestellingid like '111%'

Returns rows alright...
--

With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase
http://www.upscene.com


Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

> A few questions about LIKE and NUMERIC fields :-
>
> 1) We currently use LIKE with some of our NUMERIC fields. I know this is not
> how it should be used, so can anyone give a method to do such comparisons
> with NUMERIC fields? I need to be able to find NUMERICs starting with say 10
> (like 10, 101, 10201 etc.).
>
> 2) This used to work fine with IB 5.6. I've just upgraded to IB 6.5 and now
> the problem seems to be that I need to include a space in front of the value
> I'm looking for. eg. LIKE ' 10%' (LIKE '10%' worked fine in IB5.6). Negative
> values work fine eg. LIKE '-10%'. Any ideas about this? (I tried a LIKE
> '+10%' to no avail.)
>
> Hope my questions make sense.
>
> Regards,
> Kevin Naidoo
> M.Ying and Associates
>
>

Re: LIKE command with NUMERIC field?

#11309

Author: Kevin Naidoo
Date: Wed, 27 Nov 2002 13:05

68 lines
2708 bytes

Tried the CAST, it still requires the space in front... I'm using IB6.5
Dialect 1 (and you?)... Have tried it on two different databases on two
different machines with the same results...

Quite odd...

Regards,
Kevin


"Martijn Tonies" <m.tonies@upscene_remove.com> wrote in message
news:3de4c04c@newsgroups.borland.com...
> Hi,
>
> What if you CAST it to VARCHAR first?
>
> WHERE CAST(mynumericfield AS VARCHAR(10)) LIKE '10%'
>
>
> Actually, upon testing, it works fine with a database I have... ?!
>
> select * from BESTELLING
> where bestellingid like '111%'
>
> Returns rows alright...
> --
>
> With regards,
>
> Martijn Tonies
> InterBase Workbench - the developer tool for InterBase
> http://www.upscene.com
>
>
> Upscene Productions
> http://www.upscene.com
>
> "This is an object-oriented system.
> If we change anything, the users object."
>
> > A few questions about LIKE and NUMERIC fields :-
> >
> > 1) We currently use LIKE with some of our NUMERIC fields. I know this is
not
> > how it should be used, so can anyone give a method to do such
comparisons
> > with NUMERIC fields? I need to be able to find NUMERICs starting with
say 10
> > (like 10, 101, 10201 etc.).
> >
> > 2) This used to work fine with IB 5.6. I've just upgraded to IB 6.5 and
now
> > the problem seems to be that I need to include a space in front of the
value
> > I'm looking for. eg. LIKE ' 10%' (LIKE '10%' worked fine in IB5.6).
Negative
> > values work fine eg. LIKE '-10%'. Any ideas about this? (I tried a LIKE
> > '+10%' to no avail.)
> >
> > Hope my questions make sense.
> >
> > Regards,
> > Kevin Naidoo
> > M.Ying and Associates
> >
> >
>
>

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads