Home » SQL & PL/SQL » SQL & PL/SQL » can we use LIKE and BETWEEN same time (Oracle8)
can we use LIKE and BETWEEN same time [message #311091] Thu, 03 April 2008 06:58 Go to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
hi
is that possible v use LIKE and BETWEEN same time in SQL query, if yes then pls let me know.
Actually i have field in my table name PART in which there is statement e.g.

BL002342 DATED 29-06-2007
BL003412 DATED 29-07-2007
BL002222 DATED 29-06-2008

i want to select record from this table with range of date. Any help is really appreciable
Re: can we use LIKE and BETWEEN same time [message #311095 is a reply to message #311091] Thu, 03 April 2008 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is that possible v use LIKE and BETWEEN same time in SQL query

Yes.

Regards
Michel
Re: can we use LIKE and BETWEEN same time [message #311113 is a reply to message #311091] Thu, 03 April 2008 07:46 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
snomi wrote on Thu, 03 April 2008 07:58

i want to select record from this table with range of date. Any help is really appreciable



Then all you need is a between, not a like. And of course you are using DATEs properly (ie, using TO_DATE function on strings), correct?
Re: can we use LIKE and BETWEEN same time [message #311292 is a reply to message #311113] Thu, 03 April 2008 23:54 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
select
lgrd.client,
client.title,
sum(debit)As DBalance,
sum(credit) As CBalance,
sum(debit-credit)As Balance
from lgrd, client
where lgrd.client=client.code
AND client.CDCID='R'
AND client.AC='C'
AND client.CLCAT='I'
AND lgrd.PART BETWEEN TO_DATE ('2007/01/01', 'yyyy/mm/dd') AND TO_DATE ('2008/05/01', 'yyyy/mm/dd')
group by lgrd.client,client.title

ERROR at line 12:
ORA-01858: a non-numeric character was found where a numeric was expected

in lgrd.PART column data store in following format

PR000197 DATED 10-07-2007 - BO
PR000297 DATED 22-08-2007 - BO
PR000397 DATED 29-05-2007 - BO
PR000497 DATED 31-03-2007 - BO
PR000597 DATED 12-01-2007 - BO

n
Re: can we use LIKE and BETWEEN same time [message #311293 is a reply to message #311091] Thu, 03 April 2008 23:59 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
might it be the date formats do not match
Re: can we use LIKE and BETWEEN same time [message #311294 is a reply to message #311292] Fri, 04 April 2008 00:24 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
select
lgrd.client,
client.title,
sum(debit)As DBalance,
sum(credit) As CBalance,
sum(debit-credit)As Balance
from lgrd, client
where lgrd.client=client.code
AND client.CDCID='R'
AND client.AC='C'
AND client.CLCAT='I'
AND lgrd.PART BETWEEN TO_DATE ('01-01-2007', 'dd-mm-yyyy') AND TO_DATE ('01-05-2008', 'dd-mm-yyyy')
group by lgrd.client,client.title

i change the date format according to data which store in lgrd.PART field but error is same

ERROR at line 12:
ORA-01858: a non-numeric character was found where a numeric was expected
Re: can we use LIKE and BETWEEN same time [message #311299 is a reply to message #311294] Fri, 04 April 2008 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a DESC of your table.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: can we use LIKE and BETWEEN same time [message #311300 is a reply to message #311091] Fri, 04 April 2008 00:38 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
The format of DATE constants does not matter. The format of lgrd.PART matters.
As it is VARCHAR2, wihen comparing to dates, it is converted to date.
'PR000197 DATED 10-07-2007 - BO' does not match any NLS_DATE_FORMAT I know.
Use SUBSTR to get only the date value and issue TO_DATE with proper format.
Handle non-common situations (eg. empty date) by issuing extra condition or wrapping it into function.
Re: can we use LIKE and BETWEEN same time [message #311304 is a reply to message #311299] Fri, 04 April 2008 00:46 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
Here is my table DESC

Name Null? Type
------------------------------- -------- ----
CAT NOT NULL VARCHAR2(3)
VNO NOT NULL NUMBER(7)
CODE NOT NULL VARCHAR2(7)
PART NOT NULL VARCHAR2(40)
CHQSLIP VARCHAR2(15)
DEBIT NUMBER(14,2)
CREDIT NUMBER(14,2)
DEALER VARCHAR2(3)
CLIENT VARCHAR2(6)
SCAT NOT NULL VARCHAR2(2)
CUSER VARCHAR2(20)
CDATE VARCHAR2(19)

Re: can we use LIKE and BETWEEN same time [message #311306 is a reply to message #311304] Fri, 04 April 2008 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: can we use LIKE and BETWEEN same time [message #311315 is a reply to message #311306] Fri, 04 April 2008 01:22 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
i use SQL formatter but when i past at here on forum it disorder my formatting
Re: can we use LIKE and BETWEEN same time [message #311318 is a reply to message #311315] Fri, 04 April 2008 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use the guide and learn how to use code tags.

Regards
Michel
Re: can we use LIKE and BETWEEN same time [message #311319 is a reply to message #311318] Fri, 04 April 2008 01:41 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
problem is not formatting .... dude
pls leave formatting ... n pls ... help me out
Re: can we use LIKE and BETWEEN same time [message #311321 is a reply to message #311319] Fri, 04 April 2008 01:46 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
snomi wrote on Fri, 04 April 2008 08:41
problem is not formatting .... dude
pls leave formatting ... n pls ... help me out


Please
adhere
to
the
forum
guidelines.
Why?
It
is
just
a
small
effort
we're
asking
you,
but
it
would
make
your
post
at
least
readable
for
others.

And your question has been answered in one of the previous posts.

Have at least the courtesy to call Michel "Sir" and not "dude" Laughing
Re: can we use LIKE and BETWEEN same time [message #311322 is a reply to message #311319] Fri, 04 April 2008 01:46 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Problem IS formatting ... dude. Anther problem is using IM speak, which speaks to a third problem i.e. your inability to follow guidelines. If you cannot follow simple instructions on how to format a post so that it is readable, then how can we expect you to follow instructions on how to solve your problem? Remember, the onus is on you to post correctly structured and formatted questions. If you refuse to do this, the likelihood is that you will not get an answer. The ball is in your court.
Re: can we use LIKE and BETWEEN same time [message #311323 is a reply to message #311091] Fri, 04 April 2008 01:47 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> but when i past at here on forum it disorder my formatting
It is because of proportional font used for text. For code, use non-proportional text, as described in the link Michel gave you.

> help me out
I have nothing to add to my previous post. Or do you want somebody to count, on which position does the date literal start in lgrd.PART column, in that non-formatted text?

[Edit: modified last sentence]

[Updated on: Fri, 04 April 2008 01:51]

Report message to a moderator

Re: can we use LIKE and BETWEEN same time [message #311326 is a reply to message #311323] Fri, 04 April 2008 01:56 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
ok
i will try to formatting
Re: can we use LIKE and BETWEEN same time [message #311328 is a reply to message #311326] Fri, 04 April 2008 01:57 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
sorry michel for calling u dude ... i take my words back if u feel it
Re: can we use LIKE and BETWEEN same time [message #311331 is a reply to message #311328] Fri, 04 April 2008 02:02 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
Following is my SQL
SELECT   lgrd.Client,
         Client.Title,
         SUM(Debit) AS dBalance,
         SUM(Credit) AS cBalance,
         SUM(Debit - Credit) AS Balance
FROM     lgrd,
         Client
WHERE    lgrd.Client = Client.Code
         AND Client.cdcId = 'R'
         AND Client.ac = 'C'
         AND Client.clCat = 'I'
         AND lgrd.Part BETWEEN To_date('01-01-2007','dd-mm-yyyy')
                               AND To_date('01-05-2008','dd-mm-yyyy')
GROUP BY lgrd.Client,
         Client.Title



Follwing is my table DESC
Name                            Null?    Type
------------------------------- -------- ----
CAT                             NOT NULL VARCHAR2(3)
VNO                             NOT NULL NUMBER(7)
CODE                            NOT NULL VARCHAR2(7)
PART                            NOT NULL VARCHAR2(40)
CHQSLIP                                  VARCHAR2(15)
DEBIT                                    NUMBER(14,2)
CREDIT                                   NUMBER(14,2)
DEALER                                   VARCHAR2(3)
CLIENT                                   VARCHAR2(6)
SCAT                            NOT NULL VARCHAR2(2)
CUSER                                    VARCHAR2(20)
CDATE                                    VARCHAR2(19)
Re: can we use LIKE and BETWEEN same time [message #311332 is a reply to message #311331] Fri, 04 April 2008 02:03 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
now its ok
wat u say Sir Michel
Re: can we use LIKE and BETWEEN same time [message #311334 is a reply to message #311331] Fri, 04 April 2008 02:09 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
This is what I call a nicely formatted post Smile

And as told in one of the previous post:

PART                            NOT NULL VARCHAR2(40)


This shows that PART isn't a DATE, but a STRING containing a STRING that represent a DATE as your data shows:

BL002342 DATED 29-06-2007
BL003412 DATED 29-07-2007
BL002222 DATED 29-06-2008


flyboy already posted how to overcome this: you'll have to extract the date-portion from the string:

  TO_DATE( SUBSTR( PART , xxxxx ) , 'DD-MM-YYYY' )


flyboy also mentioned to consider possible exceptional situations, but that depends on how accurate your data is.

HTH
Marc


(oh, and drop the IM speak please Wink )

Re: can we use LIKE and BETWEEN same time [message #311340 is a reply to message #311334] Fri, 04 April 2008 02:27 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
SELECT   lgrd.Client,
         Client.Title,
         SUM(Debit) AS dBalance,
         SUM(Credit) AS cBalance,
         SUM(Debit - Credit) AS Balance
FROM     lgrd,
         Client
WHERE    lgrd.Client = Client.Code
         AND Client.cdcId = 'R'
         AND Client.ac = 'C'
         AND Client.clCat = 'I'
         AND lgrd.Part BETWEEN To_date(Substr(Part,16,10),'DD-MM-YYYY')
                               AND To_date(Substr(Part,16,10),'DD-MM-YYYY')
GROUP BY lgrd.Client,
         Client.Title


ERROR at line 12:
ORA-01858: a non-numeric character was found where a numeric was expected

now wat wrong in it ??? Sad

[Updated on: Fri, 04 April 2008 02:28]

Report message to a moderator

Re: can we use LIKE and BETWEEN same time [message #311341 is a reply to message #311340] Fri, 04 April 2008 02:31 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
My bet would be that there is at least one row that does not contain a valid date string in positions 16 to 25
Re: can we use LIKE and BETWEEN same time [message #311343 is a reply to message #311340] Fri, 04 April 2008 02:39 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
AND lgrd.Part BETWEEN To_date(Substr(Part,16,10),'DD-MM-YYYY')
              AND To_date(Substr(Part,16,10),'DD-MM-YYYY')


That's not the same as this:

AND lgrd.PART BETWEEN TO_DATE ('2007/01/01', 'yyyy/mm/dd')
              AND TO_DATE ('2008/05/01', 'yyyy/mm/dd')
Re: can we use LIKE and BETWEEN same time [message #311363 is a reply to message #311343] Fri, 04 April 2008 04:46 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
i think substr and to_date are PLSQL function i think its not works on SQL
Re: can we use LIKE and BETWEEN same time [message #311365 is a reply to message #311363] Fri, 04 April 2008 04:50 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
snomi wrote on Fri, 04 April 2008 11:46
i think substr and to_date are PLSQL function i think its not works on SQL


SUBSTR and TO_DATE are SQL-builtin functions, they existed even before PL/SQL was invented Razz

Try this:
AND TO_DATE(SUBSTR(lgrd.PART,16,10),'DD-MM-YYYY')
    BETWEEN TO_DATE('2007/01/01', 'yyyy/mm/dd')
    AND     TO_DATE('2008/05/01', 'yyyy/mm/dd')


But you must realise that if there is an index on LGRD.PART you wouldn't be using it because you're using a function (actually two) on that column.

[Updated on: Fri, 04 April 2008 04:51]

Report message to a moderator

Re: can we use LIKE and BETWEEN same time [message #311366 is a reply to message #311091] Fri, 04 April 2008 04:56 Go to previous messageGo to next message
shiva19apr
Messages: 6
Registered: April 2008
Junior Member
Hi,

you can use both.

use the syntax below,

SELECT * FROM TABLE1
WHERE FIELD1 LIKE '%S%'
AND DATE between '01-jan-2006' and '01-jan-2007'
Re: can we use LIKE and BETWEEN same time [message #311372 is a reply to message #311366] Fri, 04 April 2008 05:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
shiva19apr wrote on Fri, 04 April 2008 11:56
Hi,

you can use both.

use the syntax below,

SELECT * FROM TABLE1
WHERE FIELD1 LIKE '%S%'
AND DATE between '01-jan-2006' and '01-jan-2007'

Did you bother to read the problem?
Or any reply posted?
Re: can we use LIKE and BETWEEN same time [message #311382 is a reply to message #311363] Fri, 04 April 2008 05:51 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
snomi wrote on Fri, 04 April 2008 11:46
i think substr and to_date are PLSQL function i think its not works on SQL

Do not ONLY think! Whenever you doubt something, check it in the documentation, found eg. online on http://tahiti.oracle.com/.
Re: can we use LIKE and BETWEEN same time [message #311403 is a reply to message #311372] Fri, 04 April 2008 07:00 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
SELECT   lgrd.Client,
         Client.Title,
         SUM(Debit) AS dBalance,
         SUM(Credit) AS cBalance,
         SUM(Debit - Credit) AS Balance
FROM     lgrd,
         Client
WHERE    lgrd.Client = Client.Code
         AND Client.cdcId = 'R'
         AND Client.ac = 'C'
         AND Client.clCat = 'I'
         AND SCAT='PS'
         AND lgrd.PART BETWEEN to_date(substr('01-01-2007',16,10),'dd-mm-yyyy') AND to_date(substr('01-01-2009',16,10),'dd-mm-yyyy')
GROUP BY lgrd.Client,
         Client.Title


i think is there is problem in coverstion data time VARCHAR2 to DATE
Re: can we use LIKE and BETWEEN same time [message #311404 is a reply to message #311403] Fri, 04 April 2008 07:04 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
This would be my last shot at trying to make you see what the problem is Confused

This is what YOU wrote:
AND lgrd.PART
    BETWEEN to_date(substr('01-01-2007',16,10),'dd-mm-yyyy')
    AND to_date(substr('01-01-2009',16,10),'dd-mm-yyyy')


And this is what I suggested:
AND TO_DATE(SUBSTR(lgrd.PART,16,10),'DD-MM-YYYY')
    BETWEEN TO_DATE('01-01-2007', 'dd-mm-yyyy')
    AND     TO_DATE('01-01-2009', 'dd-mm-yyyy')


Do you see any difference between the two of them?
Re: can we use LIKE and BETWEEN same time [message #311407 is a reply to message #311404] Fri, 04 April 2008 07:13 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
  1  SELECT   lgrd.Client,
  2           Client.Title,
  3           SUM(Debit) AS dBalance,
  4           SUM(Credit) AS cBalance,
  5           SUM(Debit - Credit) AS Balance
  6  FROM     lgrd,
  7           Client
  8  WHERE    lgrd.Client = Client.Code
  9           AND Client.cdcId = 'R'
 10           AND Client.ac = 'C'
 11           AND Client.clCat = 'I'
 12           AND SCAT='PS'
 13          AND TO_DATE(SUBSTR(lgrd.PART,16,10),'DD-MM-YYYY')
 14      BETWEEN TO_DATE('01-01-2007', 'dd-mm-yyyy')
 15      AND     TO_DATE('01-01-2009', 'dd-mm-yyyy')
 16  GROUP BY lgrd.Client,
 17*          Client.Title
 18  /
        AND TO_DATE(SUBSTR(lgrd.PART,16,10),'DD-MM-YYYY')
                    *
ERROR at line 13:
ORA-01858: a non-numeric character was found where a numeric was expected

[Updated on: Fri, 04 April 2008 07:15]

Report message to a moderator

Re: can we use LIKE and BETWEEN same time [message #311410 is a reply to message #311407] Fri, 04 April 2008 07:20 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
instead of
AND TO_DATE(SUBSTR(lgrd.PART,16,10),'DD-MM-YYYY')
when i do this
AND TO_DATE(SUBSTR('01-01-2007',16,10),'DD-MM-YYYY')
query run successfully but not retrieve n e record wat a heck
Re: can we use LIKE and BETWEEN same time [message #311412 is a reply to message #311407] Fri, 04 April 2008 07:23 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
snomi wrote on Fri, 04 April 2008 14:13
AND TO_DATE(SUBSTR(lgrd.PART,16,10),'DD-MM-YYYY')
                    *
ERROR at line 13:
ORA-01858: a non-numeric character was found where a numeric was expected



This means that there is at least on record in the LGRD table for which the PART columns isn't of the specified format as you specified in one of your first posts.
Re: can we use LIKE and BETWEEN same time [message #311416 is a reply to message #311366] Fri, 04 April 2008 07:47 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
shiva19apr wrote on Fri, 04 April 2008 05:56
Hi,

you can use both.

use the syntax below,

SELECT * FROM TABLE1
WHERE FIELD1 LIKE '%S%'
AND DATE between '01-jan-2006' and '01-jan-2007'


Just when we think we are steering the original poster in the proper direction you come in an offer such a bad piece of advice like this? I think you need to go back to the documentation and read about the difference between a DATE and a STRING.
Re: can we use LIKE and BETWEEN same time [message #311418 is a reply to message #311412] Fri, 04 April 2008 07:55 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
MarcS wrote on Fri, 04 April 2008 07:23
snomi wrote on Fri, 04 April 2008 14:13
AND TO_DATE(SUBSTR(lgrd.PART,16,10),'DD-MM-YYYY')
                    *
ERROR at line 13:
ORA-01858: a non-numeric character was found where a numeric was expected



This means that there is at least on record in the LGRD table for which the PART columns isn't of the specified format as you specified in one of your first posts.



you are rite the pattern i define is correct for some data in PART column not for all
thanks to all member of this forum who help me out i appreciate there efforts to resolve this problem
thanks
Re: can we use LIKE and BETWEEN same time [message #311458 is a reply to message #311418] Fri, 04 April 2008 09:46 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
But the main thing is: do you _understand_ what you were taught?
Do you understand why
TO_DATE(SUBSTR('01-01-2007',16,10),'DD-MM-YYYY')
is Nonsense?
Previous Topic: please CHECK THE QUERY?
Next Topic: group by/rollup?
Goto Forum:
  


Current Time: Wed Dec 07 03:11:33 CST 2016

Total time taken to generate the page: 0.06134 seconds