sql with <> and substring function

The output of query has to return records where company is not equal to 'CABS' OR substring of company until empty space (eg CABS NUTS).The company name can the CABS, COBS, CABST , CABS NUTS , CAB

SELECT * 
  FROM records 
 WHERE UPPER(SUBSTR(company, 0, (INSTR(compa开发者_如何学Cny,' ')-1))) <> 'CABS' 
    OR COMPANY <> 'CABS'

But the above query is returing CABS NUTS along with COBS , CAB.

I tried using "LIKE CABS" it looks fine but if the company name is "CAB" it will not return "CABS" and CABS NUTS because of like. So LIKE is completely ruled out.

Can anyone please suggest me.

So you want all records where the first 4 characters of the Company field are not "CABS". Okay.

WHERE left(company, 4) != 'CABS'

SELECT
  *
FROM
  Records
WHERE
  LEFT(Company, 4) <> 'CABS'
  AND Company <> 'CABS'

Note: Basic TSQL String Comparison is case-insensitive

Can quite work out which ones you do want returns, but have you considered LIKE 'CABS %'

select * from records where company NOT IN (SELECT company 
FROM records 
WHERE UPPER(SUBSTR(company, 0, (INSTR(company,' ')-1))) = 'CABS' 
OR COMPANY = 'CABS')

I think this will fetch the desired records from the records table

RECORDS:

COMPANY
=====================
CAB
CABST
COBS

First, I think you should use AND instead of OR in your compound condition.

Second, you could simplify the condition this way:

WHERE UPPER(SUBSTR(company, 0, (INSTR(company || ' ',' ') - 1))) <> 'CABS' 

That is, the company <> 'CABS' part is not needed in this case.

The problem you are getting comes about because the result of the SUBSTR is null if there is not a space. And thanks to three value logic, the result of some_var <> NULL is NULL, rather than TRUE as you might expect.

And example of this is shown by the query below:

with mytab as (
  select 1 as myval from dual union all
  select 2 as myval from dual union all
  select null as myval from dual
)
select *
from mytab
where myval = 1
union all
select *
from mytab
where myval <> 1

This example will only return two rows rather than three rows that you might expect.

There are several ways to rewrite the condition to make it ignore the null result from the substr function. These are listed below. However, as mentioned by one of the other respondents, the two conditions need to be joined using the AND operator rather than OR.

Firstly, you could explicitly check that the column has a space in it using the set of conditions below:

(INSTR(company,' ') = 0 or 
 UPPER(SUBSTR(company, 0, (INSTR(company,' ')-1))) <> 'CABS') and 
COMPANY <> 'CABS'

Another option would be to use the LNNVL function. This is a function that I only recently found out about. It return TRUE from a condition when the result of the condition provided as the input is FALSE or NULL.

lnnvl(UPPER(SUBSTR(company, 0, (INSTR(company,' ')-1))) = 'CABS') and 
      COMPANY <> 'CABS'

And another option (which would probably be my preferred option) is to use the REGEXP_LIKE function. This is simple, to the point and easy to read.

WHERE not regexp_like(company, '^CABS( |$)')