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.

Don’t judge, I’m in it to survive, not compete. Unless you want to race?

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.

You can find these features documented here.

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?

/ords/admin/strava/activities/?q={"activity_type":{"$instr":"RoW"}}
The input string is treated as case insensitive – we’ll find occurrences of those letters, upper or lower.

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?

Yes, we can store and retrieve 🚣 emojis, legacy my 🍑

Hmm, can I actually look for that specific emoji? Yup!

ords/admin/strava/activities/?q={"description":{"$instr":"🚣"}}

Whilst the case of the text doesn’t matter, diacritics remain distinct. So e <> é <> è <> ē.

Just a test record…

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.

ords/admin/brews/?q={"beer_type":{"$like":"%Imperial%"}}
This one is pretty similar to $instr, but…

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.

ords/admin/brews/?q={"beer_name":{"$like":"%Coffee%Choc%"}}
There’s a lot going on with this one, and it’s also one of my favorite beers. ’22 release just came out!

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.

thatjeffsmith
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

9 Comments

  1. Avatar

    Hi Jeff,

    is there interest in supporting oracle text predicates in query filters?

  2. Avatar

    In my app I have one search field and it’s used to filter on 5 fields with q parameter of “autorested” table. One of the fields is a date and seeing it in json as string made me hope to be able to filter as string.
    Funny thing is that some value is working but it’s hard to understand what is matching and why

    • thatjeffsmith

      there is no concept of a DATE in JSON…To represent dates in JavaScript, JSON uses ISO 8601 string format to encode dates as a string.

      The values are stored as DATEs in the database, and your query parameters are transformed into WHERE clauses, so they need to be valid for the data type in play.

    • Avatar

      “query parameters are transformed into WHERE clauses” seems OK, but I have different results on sqldeveloper and in app calling the REST API.
      For example in sqldeveloper I can select with
      SELECT * FROM table where instr(dataora, ‘-02-‘)0
      SELECT * FROM table where dataora like ‘%-01-%’
      and get the records with months 02 and 01
      but when queried with REST API with
      q={“dataora”: {“$instr”: “-01-“}}
      q={“dataora”: {“$like”: “%-02-%”}}
      the result is always
      {
      “items”: [],
      “hasMore”: false,
      “limit”: 25,
      “offset”: 0,
      “count”: 0,
      “links”: [
      {
      “rel”: “self”,
      …cut

      Here’s the funny thing:
      calling the same autorest get API with
      q={“dataora”: {“$instr”: “18”}}
      I get the dates with day 18
      and with
      {“dataora”: {“$instr”: “20”}}
      I get the dates with day 20
      like the only filter working is on the day part of the date,
      same query with year is giving 0 results

    • thatjeffsmith

      Use the proper date functions when working with dates, like between. The way you’re doing it, you’re relying on implicit text conversion and that’s not reliable, esp when date formats change, which is probably what you’re running into.

    • Avatar

      You are right
      {“dataora”: {“$instr”: “jan”}}
      is filtering all January dates.
      Now it’s clear, so autorest is not enough anymore ,I’ll create a GET module.
      Thanks Jeff

  3. Avatar

    How filtering date fields works? I tried $instr and $like but no success

Reply To Emiliano Cancel Reply

RSS
Follow by Email
LinkedIn
Share