Converting strings to NUMBER

[rohanem]

Hi,
I have a question regarding converting strings to number.
I have a table "A" which has 2 columns and values stored as following-

COL1 VARCHAR2(10)
COL2 VARCHAR2(20)

select * from A where ..

COl1 COl2
--- -----
parm1 abcd
parm2 1234,5678

I have another table "B" which has values from the COL2 column from table "A", but in the following format-

COL2 NUMBER(2)

select * from B

COL2
----
1234
5678

Now if I want to select from table "B" saying -

select * from B where COL2 in(select COL2 from A where COL1='parm1')

It does not return any rows since it takes '1234,5678' as a single string and is unable to find it in B.
Is there a way to sort of seperate 1234 and 5678 in table A and do a successful search for them in table B?

Thanks a lot
Rohan

 

[SantaMufasa]

Rohan,

Please place your need in some context so that we can produce the best/most viable solution for you:

1) Is this a one-time query or will it be re-used over and over as part of an application?
2) Are you looking to display-only the results or will the results of the query "continue on" to a non-display use such as being passed along to a sub-query?
3) Are the results destined for the screen or for outputting to a file?
4) Is the environment strictly SQL*Plus or are you using a third-party tool such as Crystal Reports, TOAD, et cetera?
5) How many rows are in tables A and B?
6) Can there be 0, 1, or many rows in table A for each row in table B?
7) Can there be 0, 1, or many rows in table B for each row in table A?

Your answers to these clarifications will help us to help you resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[rohanem]

Mufasa, thanks for your response..Here are the answers to your questions-

1) The query may be used again as a simple ad-hoc query.
2) I am looking only to display the results
3) The results are simply destined for standard output
4) I was trying to run the queries from TOAD
5) Table B always has more records than table A

For 6) and 7), it is hard to co-relate the # of rows between A and B since there is no other join condition that can be applied to them. The values in table A are listed as 1234,5678,3456,2345 in a SINGLE record. In table B, each value is in a SEPERATE record.

Thanks a lot for your help
Rohan

 

[ChillAPAC]

You can use the LIKE function:
select B.*
from B,
A
where B.COL2 LIKE A.COl2

 

[SantaMufasa]

First, Rohan, for a row to display as part of your output (according to your code), 'parm1' must appear in A.Col1 and A.Col2 must contain the entire contents of a B.Col2. I cannot see where that will happen with your sample data.

If, however, you change your condition to look for 'parm2' in A.Col1, then I believe that this code should work

select *
  from B
 where (select ','||COL2||',' from A where COL1='parm2')
         like '%,'||b.COL2||',%'
/

      COL2
----------
      1234
      5678

I surrounded both strings with commas to account for these situations:

1) where the target string is either the first or last string in A.Col2
2) where the subject string (B.Col2) is a subset of a descrete superset string in A.Col2, in which case you do not want a match...for example:

B.Col2 = 34 and A.Col2 = '1234,5678'

...would, using otherwise incomplete/incorrect code, cause an improper match.

Let us know if this is does what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]