I have 10 records in a table. Out of these records, I need to perform the following 3 operations using a single Oracle query(the reason for saying single query is that this is part of an automation framework and I need to keep a single generic query)
Operation 1: get all the 10 records
Select * from table_name where col01 like ('%')
<<10 records fetched>>
Operation 2: get records that start with the string "Tivoli"
Select * from table_name where col01 like ('Tivoli%')
<<1 record fetched>>
Operation 3: get records that DOES NOT start with "Tivoli"
<<should give 9 records>>
I am Not able to write the query for operation 3 but I do not want to use a separate NOT LIKE clause as this would make me create a separate query altogether.
Then, you can use minus set operator :
Select * from table_name -- all records
minus
Select * from table_name where col01 like 'Tivoli%'
-- records for col01 column starts with Tivoli
If all types of Tivoli( such as TIVOLI.. or tIvOli or .. ) should be included case-insensitively, then you can consider the condition as
where lower(col01) like 'tivoli%'