OraFAQ Forum: SQL & PL/SQL » Read a string char by char and compare

Home » SQL & PL/SQL » SQL & PL/SQL » Read a string char by char and compare (Oracle 11g Solaris 11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Read a string char by char and compare [message #638590]

Mon, 15 June 2015 11:40

apenkov
Messages: 20
Registered: October 2012

Junior Member

Hello,
I have table1 with one column holding numbers like:

359876500911
359877500912
359878500913
359879500914

And table2 holding number prefixes like:

359876
359877

And I want to get all numbers starting with 359, but excluding these starting with the prefixes into table2, so at the end my result to be:

359878500913
359879500914

I am not so good with Oracle, so I have no idea if this is possible and how it can be done. I was trying something with EXISTS, but it doesn't work. Please, any help.

Thanks in advance!

Atanas

Report message to a moderator

Re: Read a string char by char and compare [message #638592 is a reply to message #638590]

Mon, 15 June 2015 11:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT COL1 FROM TABLE1
WHERE SUBSTR(COL1,1,6) NOT IN SELECT COL1 FROM TABLE2;

Report message to a moderator

Re: Read a string char by char and compare [message #638593 is a reply to message #638590]

Mon, 15 June 2015 11:53

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

OK, this is a homework, so show us what you already tried and where you are stuck.

Hint: LIKE and NOT LIKE should do the trick.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data.

[Updated on: Mon, 15 June 2015 11:54]

Report message to a moderator

Re: Read a string char by char and compare [message #638604 is a reply to message #638590]

Mon, 15 June 2015 13:53

apenkov
Messages: 20
Registered: October 2012

Junior Member

Thanks Black Swan,
that didn't come to my mind.....
What about if you have different lenght of the string in table2? Let's say like this:

359876500

Thanks!
BR,
Atanas

Report message to a moderator

Re: Read a string char by char and compare [message #638605 is a reply to message #638604]

Mon, 15 June 2015 13:58

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What about posting a test case?

Report message to a moderator

Re: Read a string char by char and compare [message #638607 is a reply to message #638605]

Mon, 15 June 2015 14:17

apenkov
Messages: 20
Registered: October 2012

Junior Member

OK, it is not homework first of all....
Tables are like this:

create table table1
(a_number varchar2(40))

create table table2
(prefixes varchar2(40))

I am tring to read the string char by char from table1 and only if it is doesn't match with table2, then select it.
As said in my previous post, I have no clue how to do it with PL/SQL. But this is my other alternative on DUP /Data User Processing - something mixed(Java and C++)/:

size = sezeoff(get.theSourse)
while size > 0
if(lookup(the.Sourse, result, size)
theSource == theTarget
size = -1size

And thanks!

Report message to a moderator

Re: Read a string char by char and compare [message #638608 is a reply to message #638607]

Mon, 15 June 2015 14:25

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Tables are like this:

And the INSERT statements for the data are?

Quote:

I have no clue how to do it with PL/SQL.

Don't do in PL/SQL what you can do in SQL.
You can read Join.

Report message to a moderator

Re: Read a string char by char and compare [message #638609 is a reply to message #638608]

Mon, 15 June 2015 14:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have no clue how to do it with PL/SQL. But this is my other alternative on DUP /Data User Processing - something mixed(Java and C++)/:

in Oracle, data is read from disk whole blocks at a time.
the smallest granularity to read data is a whole row.
As was previously stated, never do in PL/SQL that which can be done in plain SQL.

Report message to a moderator

Re: Read a string char by char and compare [message #638610 is a reply to message #638608]

Mon, 15 June 2015 14:44

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

insert into table1 values(359876500911);
insert into table1 values(359877500912);
insert into table1 values(359878500913);
insert into table1 values(359879500914);
insert into table2 values(359876);
insert into table2 values(359877);
commit;

SQL> select * from table1;
A_NUMBER
----------------------------------------
359876500911
359877500912
359878500913
359879500914

4 rows selected.

SQL> select * from table2;
PREFIXES
----------------------------------------
359876
359877

2 rows selected.

SQL> select t1.a_number
  2  from table1 t1 left outer join table2 t2
  3         on t1.a_number like t2.prefixes||'%'
  4  where t2.prefixes is null
  5  /
A_NUMBER
----------------------------------------
359878500913
359879500914

2 rows selected.

Report message to a moderator

Re: Read a string char by char and compare [message #638611 is a reply to message #638610]

Mon, 15 June 2015 15:33

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

Data magic:

SQL> insert into table1 values('777123456789');

1 row created.

SQL> select t1.a_number
  2  from table1 t1 left outer join table2 t2
  3         on t1.a_number like t2.prefixes||'%'
  4  where t2.prefixes is null
  5  /

A_NUMBER
----------------------------------------
359878500913
359879500914
777123456789

SQL> select t1.a_number
  2  from table1 t1 left outer join table2 t2
  3         on t1.a_number like t2.prefixes||'%'
  4  where t2.prefixes is null
  5    and a_number like '359%'
  6  /

A_NUMBER
----------------------------------------
359878500913
359879500914

SQL> select  *
  2    from  table1
  3    where a_number like '359%'
  4      and not exists(
  5                     select  1
  6                       from  table2
  7                       where a_number like prefixes || '%'
  8                    )
  9  /

A_NUMBER
----------------------------------------
359878500913
359879500914

SQL>

SY.

Report message to a moderator