| Finding a valid operand between two values in a formula [message #601214] |
Mon, 18 November 2013 13:01  |
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
|
Member |
|
|
Hi ,
I am trying to validate a formula by checking if a valid operand ( + - * / ) is present between two values.
Below is the sample data.
CREATE TABLE STRING_PARSE
(
ID NUMBER,
formula clob
);
INSERT INTO STRING_PARSE (ID, FORMULA) VALUES (1,'(ACYDYNNOTRANS_BBP_200_10_10 + ACYDYNNOTRANS_BBP_200_10_20 + ACYDYNNOTRANS_BBP_200_20 + ACYDYNNOTRANS_BBP_200_25)');
INSERT INTO STRING_PARSE (ID, FORMULA) VALUES (2,'UCYDYNNOTRANS_QUA_70');
INSERT INTO STRING_PARSE (ID, FORMULA) VALUES (3,'(ACYDYNNOTRANS_SHIP_PU / CONST_100)');
INSERT INTO STRING_PARSE (ID, FORMULA) VALUES (4,'((ACYDYNNOTRANS_BBP_200_10_10 + ACYDYNNOTRANS_SHIP_PU) * CONST_100)');
INSERT INTO STRING_PARSE (ID, FORMULA) VALUES (5,'(((ACYDYNNOTRANS_BBP_200_10_10 ACYDYNNOTRANS_BBP_200_10_20 - ACYDYNNOTRANS_BBP_200_20 + ACYDYNNOTRANS_BBP_200_25)) * CONST_100)');
select * from STRING_PARSE;
1 (ACYDYNNOTRANS_BBP_200_10_10 + ACYDYNNOTRANS_BBP_200_10_20 + ACYDYNNOTRANS_BBP_200_20 + ACYDYNNOTRANS_BBP_200_25)
2 UCYDYNNOTRANS_QUA_70
3 (ACYDYNNOTRANS_SHIP_PU / CONST_100)
4 ((ACYDYNNOTRANS_BBP_200_10_10 + ACYDYNNOTRANS_SHIP_PU) * CONST_100)
5 (((ACYDYNNOTRANS_BBP_200_10_10 ACYDYNNOTRANS_BBP_200_10_20 - ACYDYNNOTRANS_BBP_200_20 + ACYDYNNOTRANS_BBP_200_25)) * CONST_100)
In the above result the 5th row the value of the formula is missing a operand betweent he first and the second value.
A query which can parse through the values and should find if any operand is missing. Please help me with a simple algorithm, All I am ending up is more of a complex loop thing which is not providing the exact result.
Let me know if there is any inbuilt function in Oracle which can also be used for this.
Thanks
|
|
|
|
|
|
| Re: Finding a valid operand between two values in a formula [message #601276 is a reply to message #601216] |
Tue, 19 November 2013 02:35  |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Do you know the variable names involved?
When you reaplace all variable names with "1" an then run the string between a select .. and a .. from dual you can "abuse" the already existing SQL compiler to check if the formula is valid.
SQL> DECLARE
2 v_tmp_sql VARCHAR2(4000);
3 v_tmp_value NUMBER;
4 BEGIN
5
6 FOR l IN (
7 SELECT '1+1' formula FROM dual
8 UNION ALL
9 SELECT '1-1+1*4' FROM dual
10 UNION all
11 SELECT '1 1+1/' FROM dual
12 UNION all
13 SELECT '1 F 1/' FROM dual
14 UNION all
15 SELECT '1 / 1' FROM dual
16
17
18 ) LOOP
19 Dbms_Output.put_line('Formula : ' || l.formula);
20
21 v_tmp_sql := 'SELECT ' || l.formula || ' from dual';
22
23 begin
24
25 EXECUTE IMMEDIATE v_tmp_sql INTO v_tmp_value;
26 Dbms_Output.put_line(' -> OK');
27 EXCEPTION WHEN OTHERS THEN
28 Dbms_Output.put_line(' -> FAIL');
29 end;
30 END LOOP;
31
32 END;
33 /
Formula : 1+1
-> OK
Formula : 1-1+1*4
-> OK
Formula : 1 1+1/
-> FAIL
Formula : 1 F 1/
-> FAIL
Formula : 1 / 1
-> OK
PL/SQL procedure successfully completed. |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
