CODEWITHSUNDEEP
sqlamazon-redshift
Sumit Jain
Sumit Jain

Reputation: 377

Retrieve numeric range output from varchar column in redshift sql

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

Upvotes: 0

Views: 832

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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.

Upvotes: 0

Related Questions