Hi Tom,

Need your help !

Please find the LiveSQL link

Thanks in Advance !

I have a table st_exp with s_desc column only, user has option to search by s_desc criteria.

Scenario :The user may enter text critiria LIKE and NOT LIKE in the same single input field.

Am using REGEXP_LIKE for searching from the query.

sample data below : (contains all records)

select * from test_exp

S_DESC
------
NOK D8StorePQRST
NOK 8.0.1 D81SupiriorCoachStorePUJI
NOK  D81CoachStorePUJI
NOK  D81TesterCoachStorePUJI
NOK  D81RacketPUJI
D81QACoachStorePUJI
NOK D81QAPatchPUJI

scenario 1:
the user want to retrieve records which contains "D8" and "Store"

query 1: (expected result)

select s_desc from test_exp where REGEXP_LIKE(s_desc,'[D81]*(Store)')

S_DESC
-------
NOK D8StorePQRST
NOK 8.0.1 D81SupiriorCoachStorePUJI
NOK  D81CoachStorePUJI
NOK  D81TesterCoachStorePUJI
D81QACoachStorePUJI

scenario 2:
the user want to retrieve records which contains(LIKE) 'D8' and 'Store' but not contain (NOT LIKE) 'Tester' and 'QA'

query 2: (Not expected result)

select s_desc from test_exp where REGEXP_LIKE(s_desc,'[D81][^(Tester|QA)]*(Store)')

S_DESC
--------
NOK D8StorePQRST
NOK  D81CoachStorePUJI

expected 3 records but result shows only 2 records.
But the value "NOK 8.0.1 D81SupiriorCoachStorePUJI" is not retrieved by the query.

definition :
[^] Used to specify a nonmatching list where you are trying to match any character except for the ones in the list.

Please guide me for further accurate results.

Thanks & Regards
Khan
India

[^(Tester|QA)]*

[D81]

NOK 8.0.1 D81SupiriorCoachStorePUJI

select s_desc from test_exp 
where  s_desc like '%D8%Store%'
and    s_desc not like '%Tester%'
and    s_desc not like '%QA%';

S_DESC                                
NOK 8.0.1 D81SupiriorCoachStorePUJI   
NOK  D81CoachStorePUJI                
NOK D8StorePQRST