Can we use "NOT LIKE " in sql

Question

Can we use "NOT LIKE '%abc%'" just opposite of LIKE '%abc%' ? i tried and got some result but dont look like correct!!

Is there anything similar to regex in SQL.

Eg:

i hae a table with 3 field.

id  name  address
1    xyz    1234 abcd 
2    abc    nomans land
3    omg    #123 new-york
3    nom    $123 &7up

can i fetch the address **with special characters with out checking each special character one by one . How

By googling I discovered that even sql server does have some support of it. But it is not widely used. Regex row lookup is expensive. — Oybek, Feb 16 '12 at 06:03

score 6 · Answer 1 · answered Feb 16 '12 at 09:12

In SQL Server. if you wanted addresses that contained characters other than alphanumerics and spaces:

address LIKE '%[^0-9a-zA-Z ]%';

noting the ^ character means "any single character not within the specified range". Not sure if something very similar is possible in DB2.

score 5 · Accepted Answer · answered Feb 15 '12 at 21:13

5

Sure, look here. Also NOT LIKE is supported.

answered Feb 15 '12 at 21:13

Otávio Décio

73,752
17
161
228

2

Just to complete the answer, [Related question](http://stackoverflow.com/questions/4763757/regular-expressions-in-db2-sql); – Oybek Feb 15 '12 at 21:14
4

Shocking that a link at ibm.com has survived so long. In case the link goes dead, here is some helpful text [preceded by the navigation] for locating it again: developerWorks->Technical topics->Information Management->Technical library **Bringing the Power of Regular Expression Matching to SQL** The ability to find patterns in a string is a common scenario in many text-based applications. This article describes how to extend DB2 to integrate a library for regular expression matching into DB2, thereby making the task much easier and more efficient. Knut StolzeIBM Germany 23 January 2003 – CRPence Aug 11 '16 at 16:15

Lonnie Best · Answer 3 · 2016-08-01T21:54:18.427

4

In db2 (version 9.7.900.250), I've successfully specified "not like" this way:

select * from orders
where not(orders.order_number like 'S%')

This shows all orders where the order# does NOT start with a capital "S".

edited Aug 01 '16 at 21:54

answered Aug 01 '16 at 21:48

Lonnie Best

9,936
10
57
97

1

Been looking this for a while, thanks mate. – Syafiqur__ Oct 14 '21 at 08:50

score 2 · Answer 4 · answered Aug 02 '16 at 18:14

No description was given for what was "tried and got some result but don't look like correct!!" with regard to the Subject inquiry, but in review of the given data and the two predicates from the OP, consider the following; noting, the secondary regex inquiry is apparently already answered and accepted, so is ignored in this response:

with
  xmp (id, name, address) as
( values ( 1  ,  'xyz'  ,  '1234 abcd '    )
       , ( 2  ,  'abc'  ,  'nomans land'   )
       , ( 3  ,  'omg'  ,  '#123 new-york' )
       , ( 3  ,  'nom'  ,  '$123 &7up'     )
)
select id
from xmp
where address NOT LIKE '%abc%'

The above DB2 query should yield the set {(2), (3), (3)}; i.e. include all but the first row. Changing the predicate from address NOT LIKE '%abc%' to address LIKE '%abc%' should yield the set {(1)}; i.e. include only the first row. The specification of the predicate in either form address NOT LIKE '%abc%' or NOT (address LIKE '%abc%') should yield the same result; they are logically identical requests.

Can we use "NOT LIKE " in sql

4 Answers4