CODEWITHSUNDEEP
regexpostgresqlinteger
slevin
slevin

Reputation: 3896

Partially match integers in PostgreSQL queries

So in my PostgreSQL 10 I have a column of type integer. This column represents a code of products and it should be searched against another code or part of the code. The values of the column are made of three parts, a five-digit part and two two-digit parts. Users can search for only the first part, the first-second or first-second-third.

So, in my column I have , say 123451233 the user searches for 12345 (the first part). I want to be able to return the 123451233. Same goes if the users also searches for 1234512 or 123451233.

Unfortunately I cannot change the type of column or break the one column into three (one for every part). How can I do this? I cannot use LIKE. Maybe something like a regex for integers?

Thanks

Upvotes: 0

Views: 2340

Answers (2)

Abelisto
Abelisto

Reputation: 15624

Consider to use simple arithmetic. log(value)::int + 1 returns the number of digits in integer part of the value and using this:

value/(10^(log(value)::int-log(search_input)::int))::int

returns value truncated to the same digits number as search_input so, finally

search_input = value/(10^(log(value)::int-log(search_input)::int))::int

will make the trick.

It is more complex literally but also could be more efficient then strings manipulations.

PS: But having index like create index idx on your_table(cast(your_column as text)); search like 

select * from your_table
where cast(your_column as text) like search_input || '%';

is the best case IMO.

Upvotes: 2

klin
klin

Reputation: 121919

You do not need regex functions. Cast the integer to text and use the function left(), example:

create table my_table(code int); -- or bigint
insert into my_table values (123451233);

with input_data(input_code) as (
    values('1234512')
)

select t.*
from my_table t
cross join input_data
where left(code::text, length(input_code)) = input_code;

   code    
-----------
 123451233
(1 row)

Upvotes: 2

Related Questions