not like statement

[rkiss]

Hi,
Is there way to do the following sql statement?

---------------------------------------------------
select field1, field2, field3
from table
where field3 not like ('text1', 'text2', 'text3')
---------------------------------------------------

This does not work, but is there way to do it?

Thanks

Roman

[balajiyes]

select field1, field2, field3
from table
where field3 not like ('text1', 'text2', 'text3')

I think u trying for this

select field1, field2, field3
from table
where field3 not in ('text1', 'text2', 'text3')

[rkiss]

select field1, field2, field3
from table
where field3 not in ('text1', 'text2', 'text3')
----------------------------------------------

That statement would give me inaccurate results. Text1...., these are company names and I need to use NOT LIKE statement to minimize the margin or error.

Thanks

Roman

[stancho]

Originally posted by rkiss
select field1, field2, field3
from table
where field3 not in ('text1', 'text2', 'text3')

select field1, field2, field3
from table
where field3 not like 'text1'
or field3 not like 'text2'
or field3 not like 'text3'

???

[balajiyes]

That statement would give me inaccurate results. Text1...., these are company names and I need to use NOT LIKE statement to minimize the margin or error.

how cum you get inaccurate results?

since ur company names are 'text1', 'text2' and 'text3'. You can bring all these straight('text1', 'text2' and 'text3') into a NOT IN clause.

LIKE statement will be useful when you want to retrieve records which contain values %TEST%...

[rkiss]

select field1, field2, field3
from table
where field3 not like 'text1'
or field3 not like 'text2'
or field3 not like 'text3'
------------------------------------

That is what I am doing right now, but what happens if I have 20-30 text strings? Do I have to write "field3 not like 'text#'" for each one?

Roman

[rkiss]

how cum you get inaccurate results?
--------------------------------------------

Because company name could have INC.,LTD., etc.
when I use field3 not like 'text%' it will give more more accurace results than field3 not in 'text'

Roman

[balajiyes]

Will your company name change now and then?

say sometime you might user TEST1, TEST2 and TEST3.
sometime TEST2, TEST3 and TEST4 ???

In that case you can have a company master which stores all the company appearing in NOT IN clause. Where you can use an NOT IN or NOT EXIST caluse.

HTH