Query Number Column With q.like-type Functionality

Anvil Q&A
1

What I’m trying to do:
Search a Number-type Column in my data table while capturing all numbers that have the subset of the searched number.

so querying int(68) would bring up:

768
8688
12368
6810

What I’ve tried and what’s not working:
I can search for the actual number. I tried using q.like, but that only takes a string (Knew it was going to fail, but tried for completeness).

Is there a solution that is going over my head?

Thanks!

2

Short of an explicit loop, converting each number to a string, I don’t know of any.

If performance is an issue, I’d do it server-side, closer to the database.

1 Like
3

I can’t think of a way to do that with straight SQL, so I doubt Anvil will have any query operators that work.

Denormalizing the data table is probably your best bet. Have two columns, one for the number as a number, the other for the number as a string. Make sure your application code keeps those columns synchronized when the number changes, and then you can query on the string version using q.like.

1 Like
4

If you have direct SQL access, you can use Postgresql’s to_char function.

1 Like
5

Thank you for the great ideas.

@p.colbert I think I’ll have to go this route, but am creeping further and further to hosting myself, or paying for the dedicated plan, so I can use functionality like @owen.campbell suggests.

Question to @owen.campbell, Does that work in the WHERE condition? I’ve only seen it used in the SELECT statement. Reading the documentation, it’s not clear to me.

6

yes, it can.

(plus some more characters to make up the minimum)

1 Like
7

Not a direct answer I’m afraid as I don’t use datatables, and I have a feeling I may have misunderstood the question, but if you have direct SQL access, you can do what I think you’re asking in MySQL (and therefore, I assume, Postgresql as well) easily enough with a line like :

SELECT column FROM table WHERE column LIKE "%68%"

MySQL casts to a char if you perform a LIKE on an int. I imagine PGSQL does something similar.

2 Likes
8

If the number of rows is very small, then you can iterate through the rows in python.

If the number of rows is very high, then you should create one column with the numbers stored as string. If you have a dedicated plan, you can also optimize it for like searches by adding a triagram index on that column.
image

2 Likes