Returns character data from numeric data [message #321391] |
Tue, 20 May 2008 02:49  |
ramora
Messages: 16 Registered: November 2006 Location: hyderabad
|
Junior Member |
|
|
Hi experts,
Is there any function like in MSSQL,
example: converts an expression consisting of five digits and a decimal point to a six-position character string. The fractional part of the number is rounded to one decimal place.
SELECT STR(123.45, 6, 1)
GO
Here is the result set:
------
123.5
(1 row(s) affected)
one more example
SELECT STR(3.147) AS 'STR',
STR(3.147, 5, 2) AS '2 decimals',
STR(3.147, 5, 3) AS '3 decimals'
GO
Here is the result set:
STR 2 decimals 3 decimals
---------- ---------- ----------
3 3.15 3.147
Thanks&Regards,
Ram.
|
|
|
|
|
Re: Returns character data from numeric data [message #321398 is a reply to message #321391] |
Tue, 20 May 2008 02:58   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Depending on whether you want the output as a string or a number, use TO_CHAR or CAST:
SQL> select to_char(3.147) str
2 ,to_char(3.147,'9.99') two_dp
3 ,to_char(3.147,'9.999') three_dp
4 ,cast(3.147 as number(5,2))
5 ,cast(3.147 as number(5,3))
6 from dual;
STR TWO_D THREE_ CAST(3.147ASNUMBER(5,2)) CAST(3.147ASNUMBER(5,3))
----- ----- ------ ------------------------ ------------------------
3.147 3.15 3.147 3.15 3.147
|
|
|
Re: Returns character data from numeric data [message #335463 is a reply to message #321398] |
Tue, 22 July 2008 04:26   |
ramora
Messages: 16 Registered: November 2006 Location: hyderabad
|
Junior Member |
|
|
Hi,
i am unable to get output with these functions.
Is there any function STR like in oracle, please help me out of this problem
example:-
SELECT STR('9775679.89',6,2) ex1,STR('977567.89',6,2) ex2,STR('97756.89',6,2) ex3,STR('9775.89',6,2) ex4,STR('977.89',6,2) ex5
ex1 ex2 ex3 ex4 ex5
****** 977568 97757 9775.9 977.89
Thanks & Regards
Ram.
|
|
|
|
|
Re: Returns character data from numeric data [message #335520 is a reply to message #335468] |
Tue, 22 July 2008 07:23   |
ramora
Messages: 16 Registered: November 2006 Location: hyderabad
|
Junior Member |
|
|
hi,
when i am using select str(123456.78,6,2) in mssql, the output is 123457. For this i am using below query in oracle, output is 123456.78.
select to_char(123456.78,'999999D99') from dual.
The value(123456.78) is coming runtime.so i am unable to write this funtionality in single query.Please see the different output from STR function in the prior thread
|
|
|
|
Re: Returns character data from numeric data [message #336511 is a reply to message #335533] |
Sun, 27 July 2008 23:53   |
ramora
Messages: 16 Registered: November 2006 Location: hyderabad
|
Junior Member |
|
|
Hi Michel,
Here the value is the runtime parameter, we con't expect the same number of digits and precision. I have already send the different output with different value in threaded mail, I am unable to implement the same functionality with to_char function. Please help me.
Thanks in Advance.
Ram.
|
|
|
|