| Regular expression [message #578402] |
Wed, 27 February 2013 09:57  |
rajivn786
Messages: 92
Registered: January 2010
|
Member |
|
|
Hi,
Have 2 small tasks for regexp..where we need to extract numbers from the text & multiply & get the result.
Input field varchar2 : 5x a day x 10 days
Output : 5 * 10 = 50
Select regexp_replace('5x a day x 10 days', '[^[:digit:]]' from dual;
The code extracts numbers ..but doesn't multiply & give the result.
Have one more scenario as well.
Input field varchar2 : take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days
Output : 2 * 800 * 4 * 6 * 10 = 384000
Select regexp_replace('take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days', '[^[:digit:]]' from dual;
For the above code, if it's hours ..we need to convert into day by multiplying with the required factor to make it a day.
So, since it's 4, I had to multiply it by 6 to make it a day.
|
|
|
|
| Re: Regular expression [message #578404 is a reply to message #578402] |
Wed, 27 February 2013 11:24  |
 |
Michel Cadot
Messages: 54204
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Here are 3 ways to get the result of the multiplication (the 9 first lines are always the same ones).
DBMS_XMLGEN (also works in 10g):
SQL> with
2 data as (
3 select 'take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days' val
4 from dual
5 ),
6 inter as ( -- just to show the result of regexp_replace
7 select trim(both '*' from regexp_replace(val,'[^[:digit:]]+','*')) multiplication
8 from data
9 )
10 select multiplication,
11 to_number(
12 extractvalue(
13 dbms_xmlgen.getxmltype('select '||multiplication||' from dual'),
14 '//text()')) total
15 from inter
16 /
MULTIPLICA TOTAL
---------- ----------
2*800*4*10 64000
XMLQuery:
SQL> with
2 data as (
3 select 'take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days' val
4 from dual
5 ),
6 inter as ( -- just to show the result of regexp_replace
7 select trim(both '*' from regexp_replace(val,'[^[:digit:]]+','*')) multiplication
8 from data
9 )
10 select multiplication, xmlquery(multiplication returning content).getNumberVal() total
11 from inter
12 /
MULTIPLICA TOTAL
---------- ----------
2*800*4*10 64000
If OLAP option is installed:
SQL> with
2 data as (
3 select 'take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days' val
4 from dual
5 ),
6 inter as ( -- just to show the result of regexp_replace
7 select trim(both '*' from regexp_replace(val,'[^[:digit:]]+','*')) multiplication
8 from data
9 )
10 select multiplication, dbms_aw.eval_number(multiplication) total
11 from inter
12 /
MULTIPLICA TOTAL
---------- ----------
2*800*4*10 64000
The rest is not clear. Why "2 * 800 * 4 * 6 * 10"?
Regards
Michel
[Edit: modify the query to show the multiplication text]
[Updated on: Wed, 27 February 2013 11:32] Report message to a moderator |
|
|
|
| Re: Regular expression [message #578405 is a reply to message #578404] |
Wed, 27 February 2013 11:37 |
 |
Littlefoot
Messages: 16991
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
And here's a straightforward way to do that calculation:
SQL> with test as
2 (select '5x a day x 10 days' col
3 from dual
4 )
5 select regexp_substr(col, '\d+', 1, 1) *
6 regexp_substr(col, '\d+', 1, 2) result_1
7 from test;
RESULT_1
----------
50
SQL>
SQL> with test as
2 (select 'take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days' col
3 from dual
4 )
5 select regexp_substr(col, '\d+', 1, 1) *
6 regexp_substr(col, '\d+', 1, 2) *
7 regexp_substr(col, '\d+', 1, 3) * 24 / regexp_substr(col, '\d+', 1, 3) *
8 regexp_substr(col, '\d+', 1, 4) result_2
9 from test;
RESULT_2
----------
384000
SQL> |
|
|
|
| Re: Regular expression [message #578406 is a reply to message #578404] |
Wed, 27 February 2013 11:39 |
rajivn786
Messages: 92
Registered: January 2010
|
Member |
|
|
Thanks Mike..That's helpful.
The reason , I'm multiplying it by 6 is to convert number of hours into a day.
Since it's 4 hours, it's 4*6..which makes it a day.
That means the patient took 6 pills per day.
|
|
|
|
|
|
| Re: Regular expression [message #578409 is a reply to message #578408] |
Wed, 27 February 2013 11:55 |
rajivn786
Messages: 92
Registered: January 2010
|
Member |
|
|
Usually it will be one of the following for hours (multiples of 24) -- 2,3,4,6,8 ..
Can we modify the below query to include all the above combinations if the text has hours in it??
Ex : If it's 2 multiply by 12,
3 multiply by 8 etc...
|
|
|
|
| Re: Regular expression [message #578410 is a reply to message #578409] |
Wed, 27 February 2013 12:38 |
|
Michel Cadot
Messages: 54204
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Assuming there is always "X hours":
SQL> with
2 data as (
3 select 'take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days' val
4 from dual
5 ),
6 fixing as (
7 select regexp_replace(val, '([[:digit:]]+ hours)', '24/\1') val
8 from data
9 ),
10 inter as ( -- just to show the result of regexp_replace
11 select trim(both '*' from regexp_replace(val,'[^[:digit:]/]+','*')) multiplication
12 from fixing
13 )
14 select multiplication, dbms_aw.eval_number(multiplication) total
15 from inter
16 /
MULTIPLICATIO TOTAL
------------- ----------
2*800*24/4*10 96000
Regards
Michel
|
|
|
|
|
|
|
|
| Re: Regular expression [message #578413 is a reply to message #578402] |
Wed, 27 February 2013 13:57 |
rajivn786
Messages: 92
Registered: January 2010
|
Member |
|
|
Mike,
I'm using dbms_xmlgen method. There are some cases in my data where it's completely text.
I can keep a where condition to filter the records.
But may I know what should be added to the query to handle text as it gives me error.
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
