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

[mansi sharma]

create table mail3(email varchar(50))
insert into mail3 values('mansi.s [e-mail]. in')
insert into mail3 values('mansi.s harma+yahoo.co. in')
insert into mail3 values('mansi.s harma^yahoo.co. in')
insert into mail3 values('mansi.s harma*yahoo.co. in')
insert into mail3 values('mansi.s harma&yahoo.co. in')
insert into mail3 values('mansi.s harma!yahoo.co. in')
insert into mail3 values('mansi&s harma!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

[deepuv04]

create table mail3(email varchar(50))
insert into mail3 values('mansi.s [e-mail]. in')
insert into mail3 values('mansi.s harma+yahoo.co. in')
insert into mail3 values('mansi.s harma^yahoo.co. in')
insert into mail3 values('mansi.s harma*yahoo.co. in')
insert into mail3 values('mansi.s harma&yahoo.co. in')
insert into mail3 values('mansi.s harma!yahoo.co. in')
insert into mail3 values('mansi&s harma!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

select * from mail3 
where email like '%+%' or email like '%*%' or
email like '%^%' or email like '%&%'
 
or simply use the following query 
 
select * from mail3 
where email not like '%@%'
 

[mansi sharma]

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.

[Marcotte]

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 "@".

[mansi sharma]

I didn't understand what r u trying to say.

[ck9663]

He's saying try to use AND instead of OR

-- CK

[mansi sharma]

Thanks ck,
I Got it.