Home » SQL & PL/SQL » SQL & PL/SQL » escape clause
escape clause [message #198230] Mon, 16 October 2006 04:16 Go to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hi all,

1)when i use _ for email id those who having underscore.i m getting answer but for non underscore i m not geeting rows selected??
what i made wrong in this query?
please make my query correct.

SELECT * FROM RAJU WHERE EMAIL_ID LIKE'%\%%' ESCAPE '\';

no rows selected




SELECT * FROM RAJU WHERE EMAIL_ID LIKE'%S_%' ESCAPE 'S'

CLIENT NAME             CITY   STATE             SAL
------ ---------------- ------ ---------- ----------
EMAIL_ID
------------------------------
C00001 Ivan Bayross     Bombay Maharastra     150000
ashish_pass1@yahoo.com

C00004 Basu Navindgi    Bombay Maharastra     400000
ashish_kumar_80@yahoo.co.in

C00005 Ravi Sreedharan  Delhi  delhi            4000
yas_kumar1980@yahoo.co.in


CLIENT NAME             CITY   STATE             SAL
------ ---------------- ------ ---------- ----------
EMAIL_ID
------------------------------
C00006 Rukmini          Bombay Maharastra
ashish_pass1@rediffmail.com


here rows are selected but no message of rows selected?

2)If we use set feedback4;It will excute 4 rows selected but if there millions or rows at that time what would we suppose to do?

thanxx

[mod-edit]applied code tags.

[Updated on: Mon, 16 October 2006 04:19] by Moderator

Report message to a moderator

Re: escape clause [message #198235 is a reply to message #198230] Mon, 16 October 2006 04:27 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Well, this is what Oracle tells you about the FEEDBACK sql*plus setting:
SQL*Plus User's Guide and Reference Release 9.2 Part Number A90842-01
Displays the number of records returned by a script when a script selects at least n records. ON or OFF turns this display on or off. Turning feedback ON sets n to 1. Setting feedback to zero is equivalent to turning it OFF.
So, feedback will not 'execute 4 rows', it will give you feedback of how many rows are affected when the number is at least the number you specified in the feedback command. If you set feedback to 4 and you have 3 rows selected you will get no feedback, but the moment it is 4 or more you will get "nn records selected.". That's all.

MHE
Re: escape clause [message #198251 is a reply to message #198235] Mon, 16 October 2006 05:05 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
thanx maher,
but u didnt told me what i had made on earlier query.
why i m not getting any rows selected who dn't hav underscore
on email id?
Razz

[Updated on: Mon, 16 October 2006 05:05]

Report message to a moderator

Re: escape clause [message #198252 is a reply to message #198251] Mon, 16 October 2006 05:12 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you'd want to select those that DO NOT have an underscore in their e-mail addresses, you might try with something like this:

SELECT * FROM raju WHERE INSTR(email, '_') = 0;

[Updated on: Mon, 16 October 2006 05:13]

Report message to a moderator

Re: escape clause [message #198255 is a reply to message #198251] Mon, 16 October 2006 05:17 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ashish_pass1 wrote on Mon, 16 October 2006 12:05
thanks Maaher,
but u didnt told me what i had made on earlier query.
why i m not getting any rows selected who don't hav underscore
on email id?

If you want those without an underscore you can do what Littlefoot's suggested like this:
SQL> SELECT themail
  2  FROM  ( SELECT 'ab@mail.com' themail FROM dual UNION ALL
  3          SELECT 'c_d@mail.com' themail FROM dual UNION ALL
  4          SELECT '_ef@mail.com' themail FROM dual UNION ALL
  5          SELECT 'gh@mail.com' themail FROM dual
  6        ) my_test_table
  7  where themail not like '%\_%' escape '\'
  8  /

THEMAIL
------------
ab@mail.com
gh@mail.com


MHE
Previous Topic: Problem with ALTER TABLE...MODIFY CONSTRAINT...
Next Topic: Join two fields
Goto Forum:
  


Current Time: Sat Dec 10 14:43:16 CST 2016

Total time taken to generate the page: 0.08629 seconds