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

>> 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:


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