I have a table with column Salary Range
| Salary Range |
|---|
| $43K-$84K (LinkedIn est.) |
| $125K-$170K (Yahoo est.) |
| $10K-$30K (Glassdoor est.) |
I would like to retrieve the min and max salary from the column salary range as shown below
| Salary Range | min | max |
|---|---|---|
| $43K-$84K (Glassdoor est.) | 43 | 84 |
| $125K-$170K (Glassdoor est.) | 125 | 170 |
| $10K-$30K (Glassdoor est.) | 10 | 30 |
What is the best way to achieve this in SQL.
CodePudding user response：
Posting the answer from bigquery -
with cte as
(
select '$43K-$84K (LinkedIn est.)' as salary_range union all
select '$125K-$170K (Yahoo est.)' union all
select '$10K-$30K (Glassdoor est.)'
)select salary_range,
regexp_extract(split(salary_range,'-')[ORDINAL(1)],r'[0-9] ') min_sal,
regexp_extract(split(salary_range,'-')[ORDINAL(2)],r'[0-9] ') max_sal
from cte;
Result -
CodePudding user response：
With one regexp invocation,
SELECT salary_range, sal[OFFSET(0)] min, sal[OFFSET(1)] max
FROM cte, UNNEST([STRUCT(REGEXP_EXTRACT_ALL(salary_range, r'\$([0-9] )K') AS sal)])