英文:
Fetch between records for alphanumeric column using numeric range in PostgreSQL
问题
字母数字混合的 record_no 列包含多个记录，如 'A123-2', 'B345-1', 'C786-1', '0000B11-1'。
现在我想要通过类似以下方式获取指定范围内的记录：
select * from table where column between '122' and '786'
并且所有的记录都应该显示出来（例如 'A123-2', 'B345-1', 'C786-1', '0000B11-1'）。
如果我尝试将完整的 record_no 从 'A123-1' 到 'A786-1'，我只能得到以字母 A 开头的记录的结果。
请问有人可以帮我获取数字范围内的记录并显示所有相关的字母数字记录吗？
英文:
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?
答案1
得分: 1
我理解您想要在字符串中匹配第一个数字序列。
如果是这样，您可以执行：
where substring(col from '\d+')::int between 123 and 786
英文:
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
答案2
得分: 0
你想对连字号（-）前的值进行条件预测？可能你想进行数值比较，所以值得进行类型转换。类似这样的代码：
SELECT * FROM <table>
WHERE (CAST(SUBSTRING(record_no, 1, STRPOS(record_no, '-') - 1) AS int))
BETWEEN 123 AND 786;
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论