How to write case sensitive queries in kql

Question

I am writing the below code in KQL :-

 UserBaseTable
| where RawData contains_cs "REF "

I want the records which start with the string REF but when I run the above command I get records where in the "REF" string is coming in between also. I tried using the contains_cs also but it only works with the case sentivity not with the position of the searchable string. How do I get the desired result

Also the or operator doesnt work for me in KQL. Do we have a different usage of the "OR" operator?

Answer 1

Have a look at startswith_cs it could do what you're needing.

let T = datatable (R:string) [
'reformed',
'reformat',
'refreeze',
'refrains',
'refilled',
'reFramed',
'rEFrozen',
'REFlected',
'REFLECTS',
'REF erence'
];
T
| where R startswith_cs 'REF'

Wordlist courtesy of The Free Dictionary.

My output: |R| |-| |REFlected| |REFLECTS| |REF erence|

Answer 2

You can use the startswith_cs operator in KQL to get the records that start with the string "REF". Here is the code:

UserBaseTable
| where startswith(RawData, "REF")

This will return only the records where the "REF" string is at the beginning of the RawData field.

demo

Reference: The case-sensitive startswith string operator - Azure Data Explorer - Microsoft document