OrderBy on Range Coulmn(type string)Numerically BigQuery

user_12805356 0
Viewed 313

I have BQ table which has range column. Values are like(0-50, 100-150, 200-250,1000-1200, >=400).

when I am doing orderBy on this column I am getting it Lexicographic order like (0-50,100-150,1000-1200,200-250,>=400)

But I want it to orderBy numerically like(0-50,100-150, 200-250, >=400, 1000-1200). Could someone please help me how to create query for this.

google-bigquery
1 Answers

You can use approach as in below example

#standardSQL
with data as (
  select '0-50' range_col union all
  select '100-150' union all
  select '200-250' union all
  select '1000-1200' union all
  select '>=400' 
)
select *
from data
order by cast(regexp_extract(range_col, r'\b(\d+)\b') as int64)

with output

enter image description here

user_52219440
Related