Home » SQL & PL/SQL » SQL & PL/SQL » Returns character data from numeric data (9i)
Returns character data from numeric data [message #321391] Tue, 20 May 2008 02:49 Go to next message
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 #321392 is a reply to message #321391] Tue, 20 May 2008 02:55 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes. Have a look at to_char and its format models

[Updated on: Tue, 20 May 2008 02:58]

Report message to a moderator

Re: Returns character data from numeric data [message #321397 is a reply to message #321391] Tue, 20 May 2008 02:58 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
TO_CHAR?

SELECT TO_CHAR(3.147, '9990') AS "STR"
     , TO_CHAR(3.147, '9990D99') AS "2 decimals"
     , TO_CHAR(3.147, '9990D999') AS "3 decimals"
FROM   DUAL


MHE
Re: Returns character data from numeric data [message #321398 is a reply to message #321391] Tue, 20 May 2008 02:58 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #335468 is a reply to message #335463] Tue, 22 July 2008 04:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you bother to read this thread prior to replying to it?
If so, then show what you tried with to_char.
Re: Returns character data from numeric data [message #335472 is a reply to message #335463] Tue, 22 July 2008 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any function STR like in oracle

SQL Reference
Chapter 5 Functions
Section SQL Functions

Regards
Michel
Re: Returns character data from numeric data [message #335520 is a reply to message #335468] Tue, 22 July 2008 07:23 Go to previous messageGo to next message
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 #335533 is a reply to message #335520] Tue, 22 July 2008 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove 'D99' and you will have the same output.

Regards
Michel
Re: Returns character data from numeric data [message #336511 is a reply to message #335533] Sun, 27 July 2008 23:53 Go to previous messageGo to next message
ramora
Messages: 16
Registered: November 2006
Location: hyderabad
Junior Member
Hi Michel,
str(123456.78,6,2)

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.
Re: Returns character data from numeric data [message #336540 is a reply to message #336511] Mon, 28 July 2008 01:32 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Why don't you first study some basic In/Built functions that oracle provides.


Regards,
Rajat Ratewal

[Updated on: Mon, 28 July 2008 01:33]

Report message to a moderator

Previous Topic: error in explicit cursor
Next Topic: How to Call Multiple Session through Shell scripting
Goto Forum:
  


Current Time: Tue Dec 06 06:27:31 CST 2016

Total time taken to generate the page: 0.07737 seconds