Home » SQL & PL/SQL » SQL & PL/SQL » Syntax for similar Pattern between the tables (Oracle 10 and 11g)
Syntax for similar Pattern between the tables [message #605712] Mon, 13 January 2014 15:31 Go to next message
imransi17@gmail.com
Messages: 4
Registered: November 2013
Junior Member
Hello Friends,

I am trying to search the following two elements between the two tables. You can also view the attached jpg file for the result set.

1. where Postal Code matches (i am using = sign and it works fine)
2. and street_1 matches street_2

I am having issue with item 2 above. In addition to Postal Code, I only need to retrieve records that matches STREET_1 and STREET_2. So, in this scenario, I am getting many records from street_2 where 'TORONTO' matches between the two columns. I should only be able to see one record where row number 4 'TORONTO' matches between STREET_1 and STREET_2

Also, I don't want to use Like or exists operators. I am searching millions of records, so I don't know what would match to what.

Could you please help me with the syntax or function or pattern query etc?

thank you.
--- query ---
select a.postalcode, a.street_1, b.street_2
from Table_1 a, Table_2 b
where a.postalcode = b.postalcode

----  Result Set ----
POSTALCODE  STREET_1	STREET_2
Q2M 4V7	    QAUAN	27 TORONTO BLVD
Q2M 4V7	    ANGLE	27 TORONTO BLVD
Q2M 4V7	    LILLY	27 TORONTO BLVD
Q2M 4V7	    TORONTO     27 TORONTO BLVD
Q2M 4V7	    FRIEND	27 TORONTO BLVD
Q2M 4V7	    DUFFY	27 TORONTO BLVD
Q2M 4V7	    TICKLE	27 TORONTO BLVD
Q2M 4V7	    MAINGLE	27 TORONTO BLVD
Q2M 4V7	    KEATIN	27 TORONTO BLVD
Q2M 4V7	    BITEE	27 TORONTO BLVD
Q2M 4V7	    TAKIAQ	27 TORONTO BLVD
Q2M 4V7	    BLUE	27 TORONTO BLVD
Q2M 4V7	    VILLAGE	27 TORONTO BLVD
Q2M 4V7	    WHITECAP	27 TORONTO BLVD
Q2M 4V7	    OCEANIC	27 TORONTO BLVD
Q2M 4V7	    LOGAN	27 TORONTO BLVD
Q2M 4V7	    KARA	27 TORONTO BLVD
Q2M 4V7	    SALTEEN	27 TORONTO BLVD
Q2M 4V7	    BINGO	27 TORONTO BLVD
Q2M 4V7	    PICASA	27 TORONTO BLVD
Q2M 4V7	    WALTER	PO BOX 217            


Thanks.

imransi.


[EDITED by LF: applied [code] tags]

[Updated on: Mon, 13 January 2014 15:51] by Moderator

Report message to a moderator

Re: Syntax for similar Pattern between the tables [message #605715 is a reply to message #605712] Mon, 13 January 2014 15:46 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Not really sure what you mean. Also, with a non-formatted post, I don't know what is in which column, so unless you have missing column headers, 27 and BLVD are part either street 1 or street 2, hence, they do not match.
Do you want "similar" records or exact matches?
Re: Syntax for similar Pattern between the tables [message #605730 is a reply to message #605712] Tue, 14 January 2014 00:15 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not clear, maybe:
street_1='TORONTO' and instr(street_2,'TORONTO')!=0
or
instr(street_2,street_1)!=0
if 'TORONTO' is just an example and the question is just street_1 and street_2 have a match.

Previous Topic: Can someone help me with this
Next Topic: Search String
Goto Forum:
  


Current Time: Thu Mar 28 05:47:27 CDT 2024