Home » SQL & PL/SQL » SQL & PL/SQL » help in SQL
help in SQL [message #351341] Tue, 30 September 2008 07:33 Go to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi All,

i am Having two tables :

SQL> create table t11 ( i varchar2(10));

Table created.

insert into t11 values ( '00');
insert into t11 values ( '01');
insert into t11 values ( '10');
insert into t11 values ( '20');
insert into t11 values ( '30');

SQL> create table t12 ( j varchar2(20));

Table created.

SQL> insert into t12 values ('Asdsd100');
SQL> insert into t12 values ('gfg21jj20');
SQL> insert into t12 values ('dfdf30');
SQL> insert into t12 values ('991010');

Now my prob is i want to compare column from t11 with last numeric digits of t12 column for matching.

here is what i hav done, which is not correct . in the first case..

SQL> select t11.*,t12.* from t11,t12 
  2   where t11.i = substr(t12.j,-length(t11.i));

I          J
---------- --------------------
00         Asdsd100
20         gfg21jj20
30         dfdf30
10         991010
Re: help in SQL [message #351345 is a reply to message #351341] Tue, 30 September 2008 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry misread that the output is a wrong one and not the expected one.
So what should be the result as it is not correct for the last row either?

Regards
Michel

[Updated on: Tue, 30 September 2008 07:48]

Report message to a moderator

Re: help in SQL [message #351346 is a reply to message #351345] Tue, 30 September 2008 07:49 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi Michel,

But here the length of column I from table11 isnot always 2 ,

if u observe the below output for the first row

it is comparing ( i)"00" with last two digits of (j)"00"

but it has to compare numeric part of j "100" instead.
Re: help in SQL [message #351349 is a reply to message #351345] Tue, 30 September 2008 07:57 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
SQL> select substr('abcdd1234',length('abcdd1234')-length('1234')+1,length('1234')) from dual;

This is the hint.now go ahead.
Re: help in SQL [message #351354 is a reply to message #351349] Tue, 30 September 2008 08:14 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member


No Sharma,

It's not going to be work as needed.

See for example :

SQL> select substr('abcdd1000000',length('abcdd1000000')-length('00')+1,length('00')) from dual;

SU
--
00


expected o/p is : "1000000" should compare with "00"...

I need numeric suffix of 'abcdd1000000'..

Michel Sorry from my side ..how ever column j will be always
character's follwed by number
like this "jjjk2010","hdsj7lkl210"..
Re: help in SQL [message #351355 is a reply to message #351346] Tue, 30 September 2008 08:14 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Since you have not posted your oracle version I assume you will be in Oracle 10g or above. What you need is a regular expression to achieve what you want.
 select t11.*,t12.* from t11,t12 
  where t11.i = regexp_substr(t12.j,'*[0-9]+$',1)


Hope this helps.

Regards

Raj

P.S : This is an untested code and I am not an expert in regular expression.

[Edit:] Typo

[Updated on: Tue, 30 September 2008 08:15]

Report message to a moderator

Re: help in SQL [message #351357 is a reply to message #351355] Tue, 30 September 2008 08:17 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member


Hi Rajaram

Thanks for your Post ...

Is it possible to do the same in Oracle 8i or 9i.

"
 I mean with out using regexp_substr 
"

Re: help in SQL [message #351359 is a reply to message #351357] Tue, 30 September 2008 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Clue:
SQL> select rtrim('Asdsd100','0123456789') from dual;
RTRIM
-----
Asdsd

1 row selected.

Regards
Michel
Re: help in SQL [message #351360 is a reply to message #351357] Tue, 30 September 2008 08:30 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I hope now you understand why we always insist to give your full oracle version so that it saves your time and our time.

Regards

Raj

[Edit:] Nice one

[Updated on: Tue, 30 September 2008 08:31]

Report message to a moderator

Previous Topic: Date conversion error
Next Topic: Other Way on this
Goto Forum:
  


Current Time: Sun Dec 11 08:00:46 CST 2016

Total time taken to generate the page: 0.06580 seconds