Simple Like need help

[CassidyHunt]

Basically I have two tables:

Test    Result
-----------------------
abcdB   3316
bcdaB   3345
abcd    3316

Test
-----------------------
abcd
bcda

I need a query that will allow me to do a like statement to compare the test from the second table to the test from the first table. If it contains that exact string I want it to return the result.

I tried this:

select a.test arg_1, b.test arg_2, a.result from table1 a, table2 b
where a.test like '%' || b.test || '%'

I know I am being stupid somewhere here. Any help would be appreciated.

Thanks

Cassidy

 

[LKBrwnDBA]

For EXACT srting use equal (=) and not LIKE.

select a.test arg_1, b.test arg_2, a.result
  from table1 a, table2 b
 where a.test = b.test;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[CassidyHunt]

Basically I want a contains. So if the string is contained in its entirety inside the field, no matter if anything is before or after it, I want to find it as a match.

 

[karluk]

Try using the instr function:

select a.test arg_1, b.test arg_2, a.result from table1 a, table2 b
where instr(a.test, b.test) <> 0;

 

[LKBrwnDBA]

Ok, you had it reversed, try this:

SELECT a.TEST arg_1, b.TEST arg_2, a.RESULT
  FROM table1 a, table2 b
 WHERE b.TEST LIKE '%' || a.TEST || '%';

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

...Or maybe not?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Cassidy,

I don't see anything wrong with your original code. Does it not do what you want?:

SQL> select * from table1;

TEST      RESULT
----- ----------
abcdB       3316
bcdaB       3345
abcd        3316
wxyz        5555

SQL> select * from table2;

TEST
-----
abcd
bcda

SQL> select a.test arg_1, b.test arg_2, a.result from table1 a, table2 b
  2  where a.test like '%' || b.test || '%';

ARG_1 ARG_2     RESULT
----- ----- ----------
abcdB abcd        3316
abcd  abcd        3316
bcdaB bcda        3345

Let us know how your results differ from mine.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[CassidyHunt]

Was on the road. You know I thought it would to. However combing over the results and doing the query manually I get more some results manually that I am not getting using the query. I checked for white space and things like that but still not pulling them all. That is why I was bugged and posted. Instr returned exactly the same.

Thanks

Cassidy

 

[SantaMufasa]

...doing the query manually I get more some results manually that I am not getting using the query.

What is your assessment of how these two scenarios differ? Can you post complete examples of the two scenarios, including the actual code, the data values, and the differing results? (Sumthin's gotta be differnt. <grin>)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[CassidyHunt]

That is my thought. I will try to get something to do a comparison on.

Thanks

Cassidy