Home » SQL & PL/SQL » SQL & PL/SQL » Substr Function
Substr Function [message #191011] Mon, 04 September 2006 02:04 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
Can any one tell me , what is best to take the only value from the field x of table y.

like
table x
and fields are
12.32
-123.43
-11.43
10.99


and what to use this field to insert into another table called z of column 1 and 2
col1     col2
12.32
         123.43
          11.43
10.99


I am using a substr function but no luck..can anyone provide in any input what is best to do??
Re: Substr Function [message #191013 is a reply to message #191011] Mon, 04 September 2006 02:18 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Here's one way:
SQL> SELECT colx
  2       , DECODE(SIGN(colx), -1, to_number(null), colx) col1
  3       , DECODE(SIGN(colx), -1, abs(colx), to_number(null)) col2
  4  FROM ( SELECT   12.32 colx FROM dual UNION ALL
  5         SELECT -123.43 colx FROM dual UNION ALL
  6         SELECT  -11.43 colx FROM dual UNION ALL
  7         SELECT   10.99 colx FROM dual
  8       ) this_is_your_table
  9  /

      COLX       COL1       COL2
---------- ---------- ----------
     12.32      12.32
   -123.43                123.43
    -11.43                 11.43
     10.99      10.99
Re: Substr Function [message #191020 is a reply to message #191013] Mon, 04 September 2006 02:33 Go to previous messageGo to next message
aorehek
Messages: 52
Registered: August 2006
Member
Shorter code


SELECT CASE WHEN SIGN( amount) = 1 THEN amount END col1 ,
       CASE WHEN SIGN( amount) = -1 THEN amount END col2 
FROM x

Re: Substr Function [message #191021 is a reply to message #191020] Mon, 04 September 2006 02:37 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You are missing '0' Wink

MHE
Previous Topic: What is the best way to do this, stuck!! please help
Next Topic: Difference
Goto Forum:
  


Current Time: Sat Dec 10 18:29:00 CST 2016

Total time taken to generate the page: 0.09152 seconds