Home » SQL & PL/SQL » SQL & PL/SQL » Conversion Varchar2 to Number(2 Merged) (Oracle 11g)
Conversion Varchar2 to Number(2 Merged) [message #507388] Sat, 14 May 2011 20:26 Go to next message
parker214u
Messages: 19
Registered: February 2011
Junior Member
Hi,

I have 2 tables.The column in table A is number and Column in table B is a varchar2 datatype.I have to use the Column of table B as a filter to column of Table A.Below is the example.
create table A(Col1 number);
Inert into A values(1);
Inert into A values(2);
Inert into A values(3);
Inert into A values(4);

Create table B(Col1 Varchar2(100));
Insert into b value ('1,2,3');

Select * from A where col1 in (select col1 from b)
Error: Invalid Number

Is there a way to convert the varchar to number.The varchar field have multiple characters (numbers) seperated by commas.

Report message to a moderator

Re: Conversion Varchar2 to Number [message #507390 is a reply to message #507388] Sat, 14 May 2011 20:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is there a way to convert the varchar to number.
yes, using TO_NUMBER() function, but content of string must contain only numeric digits.

>The varchar field have multiple characters (numbers) seperated by commas.
how do you retain 3 separate values into a scalar variable.

Design is fatally flawed & you reap now what was sowed by allowing multiple values in a single column.

Report message to a moderator

Re: Conversion Varchar2 to Number [message #507391 is a reply to message #507390] Sat, 14 May 2011 20:48 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
As BlackSwan already noted you chose bad design. Change it if you can. Otherwise, use something like:

SQL> select * from a;

      COL1
----------
         1
         2
         3
         4

SQL> select * from b;

COL1
---------------------------------------------------------------
1,2,3

SQL> select  a.col1
  2    from  a,
  3          b
  4    where ',' || b.col1 || ',' like '%,' || a.col1 || ',%'
  5  /

      COL1
----------
         1
         2
         3

SQL>


SY.

Report message to a moderator

Re: Conversion Varchar2 to Number [message #507392 is a reply to message #507391] Sat, 14 May 2011 21:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Inert into A values(1);
>Inert into A values(2);
>Inert into A values(3);
>Inert into A values(4);
INSERT spelled incorrectly above & VALUES spelled incorrectly below!
>Insert into b value ('1,2,3');
SQL> select * from a where exists (select null from b where instr(b.col1,a.col1) > 0);

      COL1
----------
	 1
	 2
	 3


Report message to a moderator

Re: Conversion Varchar2 to Number [message #507393 is a reply to message #507392] Sat, 14 May 2011 21:31 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
BlackSwan wrote on Sat, 14 May 2011 22:03
[code]
SQL> select * from a where exists (select null from b where instr(b.col1,a.col1) > 0);


You must enclose both a.col1 and b.col1 in commas, otherwise:

SQL> update b set col1 = '14,2,3';

1 row updated.

SQL> select * from a where exists (select null from b where instr(b.col1,a.col1) > 0);

      COL1
----------
         1
         2
         3
         4

SQL> select  a.col1
  2    from  a,
  3          b
  4    where ',' || b.col1 || ',' like '%,' || a.col1 || ',%'
  5  /

      COL1
----------
         2
         3

SQL>


However, since both solutions compare strings, my solution will not work in cases like:

SQL> update b set col1 = '1.0,+2, 3';

1 row updated.

SQL> select  a.col1
  2    from  a,
  3          b
  4    where ',' || b.col1 || ',' like '%,' || a.col1 || ',%'
  5  /

no rows selected

SQL>


SY.

Report message to a moderator

Re: Conversion Varchar2 to Number [message #507394 is a reply to message #507393] Sat, 14 May 2011 21:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
To fix issue I showed in my pervious reply we could use XML:

SQL> select * from a
  2  /

      COL1
----------
         1
         2
         3
         4

SQL> select * from b
  2  /

COL1
----------------------------------------------------------------------------------------------------
1.0,+2, 3

SQL> select  a.col1
  2    from  a,
  3          b,
  4          table(xmlsequence(extract(xmltype('<rs><r>' || replace(b.col1,',','</r><r>') || '</r></rs>'),'/rs/*')))
  5    where a.col1 = ExtractValue(column_value,'/r')
  6  /

      COL1
----------
         1
         2
         3

SQL>


SY.

Report message to a moderator

Re: Conversion Varchar2 to Number(2 Merged) [message #507419 is a reply to message #507388] Sun, 15 May 2011 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's another example:
SQL> var mylist varchar2(100)
SQL> exec :mylist := '5,11,13,22,23,31,44,45'
SQL> with list as (
  2    select substr(:mylist,
  3                  instr(','||:mylist||',', ',', 1, rn),
  4                  instr(','||:mylist||',', ',', 1, rn+1)
  5                  - instr(','||:mylist||',', ',', 1, rn) - 1) value
  6    from (select rownum rn from dual 
  7          connect by level 
  8                       <= length(:mylist)-length(replace(:mylist,',',''))+1)
  9    )
 10  select id, valeur
 11  from t
 12  where id IN ( select value from list )
 13  order by id
 14  /
        ID USERNAME
---------- ------------------------------
         5 SYSTEM
        11 OUTLN
        22 MICHEL
        23 OPS$MCADOT101205
        31 SCOTT

5 rows selected.

Regards
Michel

Report message to a moderator

Re: Conversion Varchar2 to Number(2 Merged) [message #507515 is a reply to message #507419] Mon, 16 May 2011 09:38 Go to previous message
parker214u
Messages: 19
Registered: February 2011
Junior Member
Thanks u very much..

Report message to a moderator

Previous Topic: Index became unusable
Next Topic: Date check on a number column
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 26 02:31:54 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.