like function on a column

[ddk]

Hi,

I have 2 tables - Companies and List.

The table List has a Org column. I am trying to look for companies in the Companies table that are like the names in the Org column from the List table.

Do I need a loop function for this? and if so, can someone please help me to write the function?

Thanks,
ddk

[amitpatel66]

Hi,

I have 2 tables - Companies and List.

The table List has a Org column. I am trying to look for companies in the Companies table that are like the names in the Org column from the List table.

Do I need a loop function for this? and if so, can someone please help me to write the function?

Thanks,
ddk

Try this:

 
SELECT c.* from companies c, list l WHERE c.company LIKE '%l.org%'
/
 
SELECT c.* from companies c, list l WHERE INSTR(c.company,l.org) > 0
/
 
 

[ddk]

Try this:

 
SELECT c.* from companies c, list l WHERE c.company LIKE '%l.org%'
/
 
SELECT c.* from companies c, list l WHERE INSTR(c.company,l.org) > 0
/
 
 

this is returning more results than it should -
for an org name like 3M% from List, it is returning 3M, 3COM etc.. from the Companies table

[amitpatel66]

this is returning more results than it should -
for an org name like 3M% from List, it is returning 3M, 3COM etc.. from the Companies table

Both the queries returning more than the expected for 3M%?

For org = 3M%, it should return records atarting with 3M followed by any number of characters or numbers.

[ddk]

The Instr query: I am using the query you sent over. For an Org called 3M from the List table, I see 3M, 3COM being returned in the result set from the Companies table.

[amitpatel66]

The Instr query: I am using the query you sent over. For an Org called 3M from the List table, I see 3M, 3COM being returned in the result set from the Companies table.

Did you try the other one??

[ddk]

you know, it actually worked!!

Thanks a ton!

[amitpatel66]

you know, it actually worked!!

Thanks a ton!

Even I was wondering that why it did not work properly...
Anyways, you can post back in case of any further queries.