Home » SQL & PL/SQL » SQL & PL/SQL » Need part of a column (merged)
Need part of a column (merged) [message #312733] Wed, 09 April 2008 12:26 Go to next message
amit_803
Messages: 5
Registered: April 2008
Junior Member
Hello,

I have a column where 2 pieces of data are concatenated together seperated by '/'.
For eg. '1234456/xyz'
How can I get first half of the data using SQL ?

Thanks,
Amit
Re: Need part of a column [message #312737 is a reply to message #312733] Wed, 09 April 2008 12:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SQL Experts
Advanced Oracle SQL questions - Complex queries, DML and DDL statements. Newbies should not post to this forum!


What part of

Newbies should not post to this forum!
Newbies should not post to this forum!
Newbies should not post to this forum!
Newbies should not post to this forum!

do you NOT understand?
To number [message #312765 is a reply to message #312733] Wed, 09 April 2008 13:30 Go to previous messageGo to next message
amit_803
Messages: 5
Registered: April 2008
Junior Member
Hello,

I have a field with a number and character data concatenated together.
I could get the number part of it, however when I try to use the to_number function, the number is rounded off.
When I tried select to_num( field_name,'99.9999') from dual, I got an error ORA - 01722 - Invalid Number.

Can someone please help?

Thanks,
Amit
Re: To number [message #312767 is a reply to message #312765] Wed, 09 April 2008 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not if you don't post the table description, actual query and example of data.

Regards
Michel
Re: To number [message #312770 is a reply to message #312765] Wed, 09 April 2008 13:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So if I correctly merge the 2 topics, post your query now you surely badly took out the number.

Regards
Michel
Re: To number [message #312772 is a reply to message #312767] Wed, 09 April 2008 13:38 Go to previous messageGo to next message
amit_803
Messages: 5
Registered: April 2008
Junior Member
Sorry about that.
I am new to forums, however would want to commend them for the great help they are.

Example of the data is - "-1.234567/abc"
The field is VARCHAR2 type

The query is -
select to_number ( substr('"-1.234567/abc',1,instr('-1.234567/abc','/') -1) ,'99.999999') from dual

Hope this helps.
Thanks,
Amit
Re: Need part of a column (merged) [message #312778 is a reply to message #312733] Wed, 09 April 2008 13:55 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
 1* select  substr('"-1.234567/abc',1,instr('-1.234567/abc','/') -1)  from dual
SQL> /

SUBSTR('"
---------
"-1.23456


see the problem?
Re: Need part of a column (merged) [message #312783 is a reply to message #312778] Wed, 09 April 2008 14:06 Go to previous messageGo to next message
amit_803
Messages: 5
Registered: April 2008
Junior Member
Thanks for the help.
It worked fine.
I didn't realize that I don't need to explicitly convert the data.

Best Regards,
Amit
Re: Need part of a column (merged) [message #312784 is a reply to message #312783] Wed, 09 April 2008 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem is not in the conversion, it is in that you wrongly extract the number. See the extra " and missing 7 in Ana's result with your query.

Regards
Michel
Re: Need part of a column (merged) [message #312787 is a reply to message #312784] Wed, 09 April 2008 14:15 Go to previous messageGo to next message
amit_803
Messages: 5
Registered: April 2008
Junior Member
Ohk Got it .

I just have the last doubt here.
Sorry for all the trouble.

How can I convert a negative number into positive, but keep all the decimals intact?
I tried using the abs function, but it rounds of the decimal values.

Query :
select abs ( -1.123456) from dual.
o/p : 1.2
I need the o/p : 1.123456

Please help.
Thanks,
Amit
Re: Need part of a column (merged) [message #312789 is a reply to message #312787] Wed, 09 April 2008 14:28 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The query is correct, only your output is not, check your tool and its configuration for numbers.

Regards
Michel
Previous Topic: Trap the error code (ORA-02019, ORA-06550, ORA-00942) (merged 2 threads)
Next Topic: create new row and inserting variables as values
Goto Forum:
  


Current Time: Fri Dec 09 05:49:12 CST 2016

Total time taken to generate the page: 0.08993 seconds