Fetch between records for alphanumeric column using numeric range in PostgreSQL

Question

The alphanumeric record_no column contains multiple records like 'A123-2', 'B345-1', 'C786-1', '0000B11-1'.

Now I wanted to fetch the records with the range by using something similar to

select * from table where column between '122' and '786'

and all the records should be display (ex. 'A123-2', 'B345-1', 'C786-1', '0000B11-1').

If I try complete record_no like 'A123-1' to 'A786-1' I am getting the result only for records starting with letter A.

Someone can please help me with fetching the records with the number range and displaying all the associated alphanumeric records?

'0000B11-1' is an individual record itself no need to map. Hardly we can rename to 'B000011-1' if possible with a query as we may have multiple records with the format. — JNRaj
– JNRaj, Commented Oct 3, 2020 at 9:44

GMB · Accepted Answer · 2020-10-03 10:06:40Z

1

I understand that you want to match agains the first series of digits in the string.

If so, you can do:

where substring(col from '\d+')::int between 123 and 786

answered Oct 3, 2020 at 10:06

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Query seems to be close but its giving me ERROR: value "7852469321" is out of range for type integer SQL state: 22003

@Raj: this was not showing in your sample data, where each string has just a few digits. Use bigint instead of int.

Wow its working perfect :). Yes @GMB, You are correct I could have mention more digit numbers in sample data.

w08r · Accepted Answer · 2020-10-03 07:46:58Z

0

You want to predicate on the value between the letter and the hyphen? Probably you want to compare numerically, so worth a cast too. Something like

SELECT * FROM <table> 
WHERE ( SUBSTRING(record_no, 1,  STRPOS(record_no, '-') - 1))::int 
BETWEEN 123 AND 786;

answered Oct 3, 2020 at 7:46

w08r

1,84415 silver badges16 bronze badges

Great answer but I got ERROR: invalid input syntax for integer: "0000B11" SQL state: 22P02. I have updated the question by your answer.