Integer comparison as string

Question

Hi

I have an integer column and I want to find numbers that start with specific digits.

For example they do match if I look for '123':

1234567
123456
1234

They do not match:

23456
112345
0123445

Is the only way to handle the task by converting the Integers into Strings before doing string comparison?

Also I am using Postgre regexp_replace(text, pattern, replacement) on numbers which is very slow and inefficient way doing it.

The case is that I have large amount of data to handle this way and I am looking for the most economical way doing this.

PS. I am not looking a way how to cast integer into string.

Answer 1

The best way for performance is to store them as strings with an index on the column and use LIKE '123%'. Most other methods of solving this will likely involve a full table scan.

If you aren't allowed to change the table, you could try the following, but it's not pretty:

WHERE col = 123
   OR col BETWEEN 1230 AND 1239
   OR col BETWEEN 12300 AND 12399
   etc...

This might also result in a table scan though. You can solve by converting the OR to multiple selects and then UNION ALL them to get the final result.

Answer 2

Are you looking for a match at the start of the value? You might create a functional index like this:

CREATE INDEX my_index ON mytable(CAST(stuff AS TEXT));

It should be used by your LIKE query, but I didn't test it.

Answer 3

As a standard principle (IMHO), a database design should use a number type if and only if the field is:

1. A number you could sensibly perform maths on
2. A reference code within the database - keys etc

If it's a number in some other context - phone numbers, IP addresses etc - store it as text.

This sounds to me like your '123' is conceptually a string that just happens to only contain numbers, so if possible I'd suggest altering the design so it's stored as such.

Otherwise, I can't see a sensible way to do the comparison using it as numbers, so you'll need to convert it to strings on the fly with something like

SELECT * FROM Table WHERE CheckVar LIKE '''' + to_char(<num>,'999') + '%'