Querying all ids which have specific left-most digits-CodePudding

I have queried specific ids from a table. Now I want to find all the ids in the same table that would be postfixes for the ids I initially fetched. For example

If I fetch 1, 2, 3, 4 I want to get all the ids

| 1 | 10, 12, 13, 11, 111, 100, 1000.... |
| 2 | 20, 222, 23, 21, 20, 2999, 2000....|

and so on. I would be able to easily handle this via code but Can't seem to figure out how to proceed with Postgres, Would I have to cast the entire column as a String to make this work?

CodePudding user response：

This was too fun to pass up.

If you have search ids with multiple digits, you can use log() to figure out what the divisor should be:

with query as (
  select generate_series(1, 2, 1) as search_id
  union all
  select 20
)
select q.search_id, array_agg(t.id) as matches
  from query q
       left join my_table t
         on floor(log(t.id)) > floor(log(q.search_id))
        and floor(t.id / 10^(floor(log(t.id)) - floor(log(q.search_id)))) = q.search_id
 group by q.search_id;

db<>fiddle here

CodePudding user response：

Edit: mis-interpreted the example for the sole situation (single digit prefixes).

For this to work with any length of prefix, the where condition would be as follows:

WHERE CAST( ID/POWER(10, FLOOR(LOG(ID)) ) * POWER(10,FLOOR(LOG(selectedID))) AS integer) = selectedID

The following only works for 1 digit prefixes:

If the IDs have a small (~10) digit maximum and if they're stored as integers, you could divide by 10 multiple times in the where clause:

... 
WHERE ID/10 = selectedID
OR ID/100 = selectedID 
OR ID/1000 = selectedID
OR....

etc.

Alternatively, using LOG, CAST, FLOOR, and POWER, if the IDs can be very long:

WHERE CAST( ID / POWER(10, FLOOR(LOG(id))) AS integer) = selectedID

replacing = selectedID with IN (list of IDs) as necessary.