By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,359 Members | 1,365 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,359 IT Pros & Developers. It's quick & easy.

To pull all emails that contains +,^,*,&,!

P: 22
create table mail3(email varchar(50))
insert into mail3 values('mansi.sharma@yahoo.co.in')
insert into mail3 values('mansi.sharma+yahoo.co.in')
insert into mail3 values('mansi.sharma^yahoo.co.in')
insert into mail3 values('mansi.sharma*yahoo.co.in')
insert into mail3 values('mansi.sharma&yahoo.co.in')
insert into mail3 values('mansi.sharma!yahoo.co.in')
insert into mail3 values('mansi&sharma!yahoo.co.in')
select * from mail3

select * from mail3 where email like '%+%'
Above Query is wokimg fine for + sign.

But If I write the
select * from mail3 where email like '%+%' or '%*%'

Error is coming.Plz reply
Apr 10 '08 #1
Share this Question
Share on Google+
6 Replies


deepuv04
Expert 100+
P: 227
create table mail3(email varchar(50))
insert into mail3 values('mansi.sharma@yahoo.co.in')
insert into mail3 values('mansi.sharma+yahoo.co.in')
insert into mail3 values('mansi.sharma^yahoo.co.in')
insert into mail3 values('mansi.sharma*yahoo.co.in')
insert into mail3 values('mansi.sharma&yahoo.co.in')
insert into mail3 values('mansi.sharma!yahoo.co.in')
insert into mail3 values('mansi&sharma!yahoo.co.in')
select * from mail3

select * from mail3 where email like '%+%'
Above Query is wokimg fine for + sign.

But If I write the
select * from mail3 where email like '%+%' or '%*%'

Error is coming.Plz reply
hi,
to pull the mail ids having the given symbols you can use the following query

Expand|Select|Wrap|Line Numbers
  1. select * from mail3 
  2. where email like '%+%' or email like '%*%' or
  3. email like '%^%' or email like '%&%'
  4.  
  5. or simply use the following query 
  6.  
  7. select * from mail3 
  8. where email not like '%@%'
  9.  
Apr 10 '08 #2

P: 22
Thx,It worked.
Plz see the following query-

SELECT * FROM mail3
WHERE email NOT LIKE '%@%'
or email not like '%&%'


Instead of showing the particular records ,the above query is displaying all the records.
Apr 10 '08 #3

P: 5
Thx,It worked.
Plz see the following query-

SELECT * FROM mail3
WHERE email NOT LIKE '%@%'
or email not like '%&%'


Instead of showing the particular records ,the above query is displaying all the records.
That's because of the OR. For the email with the "&" in it, the where evaluates like this:

email NOT LIKE '%@%' --> True
email NOT LIEK '%&%' ---> False
so you have
True OR False, which evaluates to True.

Put another way, every record in the table either doesn't have a "&" or it doesn't have a "@".
Apr 10 '08 #4

P: 22
I didn't understand what r u trying to say.
Apr 10 '08 #5

ck9663
Expert 2.5K+
P: 2,878
He's saying try to use AND instead of OR

-- CK
Apr 10 '08 #6

P: 22
Thanks ck,
I Got it.
Apr 11 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.