If this topic sounds familiar, it should! We’ve talked about it a few times here already, but it’s a critical feature of ORDS and it merits multiple posts. Today I wanted to shine a quick light on:
- In string operator ($instr)
- LIKE operator ($like)
Let’s start with some sample data.
My STRAVA table has 700+ records in it, and while I’m somewhat consistent with the naming, I’m not perfect.
I’m going to show how to use these query parameters on REST APIs for this table, both using AutoREST and a Query Backed Module/GET Handler.
INSTR – Searches string for a specific substring
My GET Handler does exactly what you see above. It pulls everything. But maybe I only want to see activities where I’ve been rowing, or I went for a row?
I’m pretty consistent on the activity_type, but not so much on the activity description. Maybe let’s look for all the 10k stuff I’ve done?
Hmm, can I actually look for that specific emoji? Yup!
Whilst the case of the text doesn’t matter, diacritics remain distinct. So e <> é <> è <> ē.
LIKE – Allows wildcards to perform pattern matching
I may want to be a bit more selective on my searches. I have a specifc pattern I want to find. That’s where our old friend LIKE becomes useful.
Since it’s Monday morning, let’s switch over to my other favorite hobby and instead of a SQL based REST API, we’ll look at a AutoREST enabled table.
These types of predicates are extremely common and make for fun indexing schemes.
Let’s look for ‘Imperial’ anywhere in the BEER_TYPE column.
It’s similar to what we already look at with the $instr examples except that it’s case-sensitive, AND I pick the patterns. So ‘imperial’ <> ‘Imperial’ – and I get to pick where the wild-cards go. So ‘Imperial%’ would not pick up on ‘Double Imperial…’
Let’s get a bit more creative.
Happy Monday everyone 🙂
If you’re ever in North Carolina this time of year, and you enjoy tasting regional fare, this product out of Winston-Salem is very good – in small quantities.