I have two tables which have a field, let's call it FORM_TYPE, in which table 1's FORM_TYPE is LA-- and table 2's FORM_TYPE is LA---. Now I want to use a Where clause of Table1.FORM_TYP E = Table2.FROM_TYP E which always fails because of the extra -. I can't go change the database to make them the same. I was trying to use Where Left(Table1.FOR M_TYPE,4) = Left(Table2.FOR M_TYPE,4) so that I was basically stripping off some of the -. But it appears that Left is not usable within the select statement. I have included the exact SQL statement. The error I get is ORA-00904: "LEFT":inva lid identifier.
-
-
Hey.
You can use the LIKE operator to match one column to another, to find fields that contain the value of another column.
For example, if you wanted to find all rows that included the letters "LA", you would do:
[code=sql]WHERE col LIKE '%LA%'[/code]
The % char is a wild-card character. Surrounding a string, like I did there, means it can occur anywhere in the search string.
You can use CONCAT to build the search string using the value of another field.
[code=sql]WHERE col1 LIKE CONCAT('%', col2, '%')[/code]
So to join two tables using this as a filter, you would probably be best of doing:
[code=sql]SELECT *
FROM table1
INNER JOIN table2
ON table1.value LIKE CONCAT('%', table2.value, '%')[/code]
Comment