Help - Search - Members - Calendar
Full Version: Oracle substr and instr function
Oracle DBA Forums > Oracle > Oracle Forum
rockcandy2006
I have two tables where I am trying to combine the results with not much luck. TableA - column1,column2,column3 and table B has column4,column5,column6. tableB column6 data record is a subset of tableA column1. Example tableA column1 - 44141565 and tableB column6 would be 44. I want to match the tableB column6 value at the begging of tableA column1 value so in the example 44 would be a match and I want to get tableA column2 and 3 data for those matching records.
I think there is a way to do it using substr and instr but I can't figure it out. Anyone who is an expert on these function would appreciate the help. I did this query:
select a.column6,b.column2,b.column3
from tableA a, tableB b
where instr(a.column6,b.column1)

but it doesnt return any value gives error: Invalid relational operator

Thanks
Brian Carr
>> it doesnt return any value gives error: Invalid relational operator
That is because Oracle needs the WHERE clause to be formatted in forms of a relationship. For example where 1=1.

So do this:
CODE
select a.column2,a.column3,b.column4
from tableA a, tableB b
where instr(a.column1,b.column4) = 1;


The "=1" will meet your requirement of matching the value from tableB with the beginning value of tableA

You could also consider using a CONTAINS http://www.dba-oracle.com/t_sql_contains_clause.htm
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.