Is it possible to have "NOT LIKE" in ADQLs?

[Sanjay.Girotra]

Hi,

I understand below query is possible:

SELECT count(*) FROM synth_session_records WHERE failureType = "Test location is currently unavailable" AND measurementSpec.scheduleName REGEXP "Test-job.*"

But is this possible:

SELECT count(*) FROM synth_session_records WHERE failureType = "Test location is currently unavailable" AND measurementSpec.scheduleName REGEXP NOT-LIKE "Test-job.*"

Refer below docs:

https://docs.appdynamics.com/display/PRO45/Analytics+Synthetic+Sessions+Data

https://docs.appdynamics.com/display/PRO45/REGEXP+Operator

Thanks.

[Mika.Sasaki]

Thanks for posting the question on the Community.

NOT LIKE and REGEXP cannot be used at the same time.

dot(.)+asterisk(*) in REGEXP is equal to asterisk(*) in LIKE phrase, so you can use NOT LIKE “Test-job.*“.

“The asterisk(*) character matches zero or more characters. ” as in the document

https://docs.appdynamics.com/display/PRO45/Comparison+Operators

Please refer to the following SQL :

SELECT count() FROM synth_session_records WHERE failureType = “Test location is currently unavailable” AND measurementSpec.scheduleName NOT LIKE “Test-job.*“

Let me know if this helped you.

Many thanks,

Mika

[Sanjay.Girotra]

Thanks for the response Mika.

Although it accepted "NOT LIKE", below query worked:

SELECT count(*) FROM synth_session_records WHERE failureType = "Test location is currently unavailable" AND measurementSpec.scheduleName NOT LIKE "Equity*".

Note the missing Dot in NOT LIKE clause after Equity.

So far so good, the follow up question is how to ignore many patterns, say,

SELECT count(*) FROM synth_session_records WHERE failureType = "Test location is currently unavailable" AND measurementSpec.scheduleName NOT LIKE "Test*|Equity*|Find*"

 

 

 

[Mika.Sasaki]

Hi Sanjay,

Unfortunately, regular expression cannot be used with "NOT LIKE".

Please refer to the following SQL :

SELECT count(*) FROM synth_session_records WHERE failureType = "Test location is currently unavailable" AND (measurementSpec.scheduleName NOT LIKE "Test*" OR measurementSpec.scheduleName NOT LIKE "Equity*" OR measurementSpec.scheduleName NOT LIKE "Find*")

Let me know if this helped you.

Many thanks,

Mika