Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Like condition
Fza
post Dec 28 2011, 11:41 AM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 28-December 11
Member No.: 46,495



Hey guys, I have a problem with a query I'm trying to run. I need to match two columns containing names, first column (NAME1) contains only the surname and the second column (NAME2) contains a surname and initials, with the initials turning up on either side of the surname.

Example:
NAME1: 'Bush', 'Thomas', 'Cook', 'Smith'
NAME2: 'Bush, B.B.', 'Thomas,C.' 'Cook', 'A.A. Smith'

Basically the code I'm trying only turns up complete matches and not partials. Anyone know what I'm doing wrong or how I can improve it?

CODE
select *
from TEST
where NAME1 like ('%'||NAME2||'%');


Go to the top of the page
 
+Quote Post
merrillcd_mem
post Dec 28 2011, 12:44 PM
Post #2


Member
**

Group: Members
Posts: 29
Joined: 30-June 11
From: Memphis TN
Member No.: 45,469



What about Text? Depending how large the table is, rows, etc. It might be worth looking into.

select *
from TEST
where contains(name2,name1) > 0



QUOTE (Fza @ Dec 28 2011, 11:41 AM) *
Hey guys, I have a problem with a query I'm trying to run. I need to match two columns containing names, first column (NAME1) contains only the surname and the second column (NAME2) contains a surname and initials, with the initials turning up on either side of the surname.

Example:
NAME1: 'Bush', 'Thomas', 'Cook', 'Smith'
NAME2: 'Bush, B.B.', 'Thomas,C.' 'Cook', 'A.A. Smith'

Basically the code I'm trying only turns up complete matches and not partials. Anyone know what I'm doing wrong or how I can improve it?

CODE
select *
from TEST
where NAME1 like ('%'||NAME2||'%');

Go to the top of the page
 
+Quote Post
Fza
post Dec 29 2011, 04:39 AM
Post #3


Newbie
*

Group: Members
Posts: 2
Joined: 28-December 11
Member No.: 46,495



QUOTE (merrillcd_mem @ Dec 28 2011, 12:44 PM) *
What about Text? Depending how large the table is, rows, etc. It might be worth looking into.

select *
from TEST
where contains(name2,name1) > 0


Thanks for pointing me in the right direction, solved it using the instring function.

CODE
select  x.*
,       instr(upper(trim(NAME2)), upper(trim(NAME1)))
from TEST X;
Go to the top of the page
 
+Quote Post
burleson
post Dec 29 2011, 05:10 PM
Post #4


Advanced Member
***

Group: Members
Posts: 11,401
Joined: 26-January 04
Member No.: 13



HI
>> select x.*
, instr(upper(trim(NAME2)), upper(trim(NAME1)))

FYI, YOU CAN CREATE AN INDEX ON THIS TOO ( FUNCTIONED-BASED INDEX):

CODE
create index
   mytidx
on instr(upper(trim(NAME2))


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
paulogervasio
post Dec 29 2011, 09:34 PM
Post #5


Advanced Member
***

Group: Members
Posts: 69
Joined: 5-August 11
From: Brazil
Member No.: 45,669



CODE
select * from names where REGEXP_LIKE(name2, name);


[]'s
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd August 2014 - 06:23 AM