Retrieve numeric range output from varchar column in redshift sql

Question

I am trying to retrieve numeric range from varchar column, however not able to execute

The column sid_id is varchar and has numeric as well as alphanumeric values like

09446115979980
09446115980104
09446115981633
A1X98090900900

However, I am looking to extract only numeric values in particular range

Have already tried

sid_id as bigint:

ERROR: XX000: Invalid digit, Value 'D', Pos 14, Type: Long

sid_id as numeric:

ERROR: XX000: Invalid digit, Value 'D', Pos 14, Type: Decimal

sid_id as varchar: giving blank output

The code used is  

with tid as
(
select cast(sid_id as bigint) sid
from mf1.tb1 s                     
where 
right(sid_id,13) similar to '[0-9]{13}'
and left(sid_id,4)= '0944'
)
select tid.sid from tid
where tid.sid between 9445897133440 and 9445907133438
or tid.sid between 9446098650000 and 9446198649999

FYI.. Only last 2 lines is throwing error, which is the necessity in this code

Edit your question and provide sample data and desired results. — Gordon Linoff
Updated with sample data and desired range is mentioned in last 2 lines of code — Sumit Jain

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-06-06T12:06:18

How about just using string comparisons:

select s.sid
from mf1.tb1 s
where s.sid ~ '^[0-9]*$' and
      (s.sid between '09445897133440' and '09445907133438' or
       s.sid between '09446098650000' and '09446198649999'
      )

Interesting that this does not work with similar to, but it does with ~.

This is not 100% the same as your logic, but it probably does what you want for the data you have.

Here is a db<>fiddle using Postgres.

Retrieve numeric range output from varchar column in redshift sql

1 Answers