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?

More fun examples, using your own data to learn SQL, build REST APIs, etc.

Untappd || iTunes || Twitter || Strava || Netflix || Spotify


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.

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.

19 Comments

  1. Can we do something like this using zulu time: q={“my_date”:{“$gte”:”2023-04-18T07:28:02Z”}}
    we get a Bad Request response…

  2. Hi Jeff!
    I couldn’t find if you had made available how you got your data out of strava into your DB.
    I’d like to create a “challenge” for our running club where our members ‘s activity details would get processed , filtered and sorted .on our apex site. The strava default leadership board just doesn’t cut it. I got my clientid
    from https://www.strava.com/settings/api … I see that members will need to authorize our apex page to read activity ( https://developers.strava.com/docs/getting-started/) . I see there is a oauth2 authorization with refresh token mechanism. is there an easy/out-of-the-box way of managing this in APEX ?
    Last year we managed the Summer challenge on APEX with 100+ members submitting their activities details ( distance/time/type . This year I’d like to automate this ) daily for 50 days : https://apex.oracle.com/pls/apex/r/hejazultra/app/50-50-summer-challenge

    thanks.
    Fabrice

  3. Gabriel Silveira Reply

    Hello, I need to make an http query with ORDS, I need that when comparing the instr the value that is in the database is without spaces, for example:
    search word: “ofsear”
    value in database: Test of Search

    so that it returns that value from the database (Test of Search).

    • Gabriel Silveira

      I think I’m not doing it in the most efficient way, but explaining it better:
      I have a database with 70 thousand data, and I need a link that receives a name and performs a quick search returning all items that contain that name, I just found something about the modules, which receive a parameter and can perform this search, however I can’t find a lot of explanatory documentation, can you help me with the creation and configuration of modules for searching the database via url?

    • In my blog post is the link to the Docs. If you have a specific question, I can give a better answer.

    • Gabriel Silveira

      I understand, in my front end I need to make requests to the database to query the data, as there is a lot of data and the ORDS works with pages of 10,000 data, it takes time to read page by page to search for the specific data, so I wanted to know if through from the parameter “?q” it is possible to use a replace/regex in the compared field. Example
      in the database:
      Text: “Text for example”

      On the front end:
      “textfor”

      in order to compare and return json of data “Text for Example”
      “textfor” in “Textforexample”

  4. Hi Jeff,

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

  5. 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

    • 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.

    • Emiliano

      “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

    • 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.

    • Emiliano

      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

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

Reply To Florian Cancel Reply