Home » SQL & PL/SQL » SQL & PL/SQL » varchar to value (oracle 8i)
varchar to value [message #349491] Mon, 22 September 2008 01:28 Go to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Dear all,

I have a varchar field 'string' in which i want to put mathematical string like (1*9)+(4/2)-6.

Now i want the value to be calculated itself in other column 'value'.

i also want validation for varchar field that we can put only numbers and other mathematical sign in that.


regards,
Re: varchar to value [message #349503 is a reply to message #349491] Mon, 22 September 2008 01:57 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This problem is not related to Oracle; you'll have to parse this string, find out its components, raise an error if you find something unexpected, decide what to do with certain symbols you find, do the computing and - finally - display the result.

If you wish to learn how to do that properly, take several months and study how language processors work.
Re: varchar to value [message #349512 is a reply to message #349491] Mon, 22 September 2008 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try to execute the formula (using "execute immediate") if it raises an error then the formula is not valid.

Regards
Michel
Re: varchar to value [message #349518 is a reply to message #349491] Mon, 22 September 2008 02:27 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
some simple calculations You could do like:
DECLARE
 tMath VARCHAR2(50)  :='1+2*3';
 nErg  NUMBER;
 
BEGIN	
  EXECUTE IMMEDIATE 'SELECT '||tMath||' FROM DUAL' INTO nErg;
  dbms_output.put_line(tMath||'='||nErg);
END;

For validation You could have a look at the regular expressions:
REGEXP_INSTR, REGEXP_SUBSTR
Re: varchar to value [message #349527 is a reply to message #349518] Mon, 22 September 2008 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@_jum

Was not my answer sufficient?
Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: varchar to value [message #349551 is a reply to message #349527] Mon, 22 September 2008 03:35 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
@michel,
excuse but our answers crossed, needed some time to test the case Embarassed
BTW the hardest part (validation) remains to be done.
Re: varchar to value [message #349558 is a reply to message #349551] Mon, 22 September 2008 04:05 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
BTW the hardest part (validation) remains to be done.

From a PL/SQL point of view, I agree.
From learning point of view, I don't, as searching in the documentation will help to learn much more.

Regards
Michel
Previous Topic: Warning while creating procedure
Next Topic: Insert values into few columns
Goto Forum:
  


Current Time: Sat Dec 03 10:17:14 CST 2016

Total time taken to generate the page: 0.06633 seconds