CODEWITHSUNDEEP
sqloracle11g
Rajesh Kumar Duraisamy
Rajesh Kumar Duraisamy

Reputation: 797

Split the column and find min , max and avg value in oracle query

Let consider i have below data 

Name        Fee
Student1    Fee paid : 100rs 
Student2    Fee paid : 500rs
Student3    Fee paid : 200rs

I need to select max ,min and avg value of Fee column which need to split up by : and rs delimiter

For Example Here Result is 

Min     Max     Avg
100     500     250

Upvotes: 0

Views: 485

Answers (1)

trincot
trincot

Reputation: 350760

You could use a regular expression for that:

select min(fee_num), max(fee_num), avg(fee_num)
from (
        select to_number(regexp_substr(fee, ':\s*(\d+)\s*rs', 1, 1, null, 1)) fee_num
        from mytable
     )

The 6th argument of regexp_substr (available since Oracle 11g) allows you to return only the text matched by the capture group (i.e. \d+ in this case).

The regular expression requires a : to be optionally followed by white-space (\s*), then to have some digit(s) (\d+), again followed by optional white space, with rs following that.

Note that retrieving amounts from text columns defeats any advantage you could have from a structured database: indexes on that column won't be used, and the extraction with regular expressions comes with an overhead.

In normalised relational database design, a field contains atomic data only, not anything else. So in this case the fee column should contain the amount only and be of a numerical data type. Any other information you need to know about the fee should be stored in different column(s).

Upvotes: 2

Related Questions