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.
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"}}
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!
ords/admin/strava/activities/?q={"description":{"$instr":"🚣"}}
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.
ords/admin/brews/?q={"beer_type":{"$like":"%Imperial%"}}
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%"}}
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.
17 Comments
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
I didn’t setup a client, I just manually requested my personal data and did a 1 time import.
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).
What’s wrong with the spaces?
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.
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”
Try $instr
Hi Jeff,
is there interest in supporting oracle text predicates in query filters?
There is now, you’re the first to ask for it!
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.
“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.
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
How filtering date fields works? I tried $instr and $like but no success
Dates aren’t strings..what do you want?