Like list of terms (not a constant)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Like list of terms (not a constant)

Author

Topic

lichette
Starting Member

3 Posts

Posted - 2009-10-09 : 21:10:22

Hi,

I'm new on this forum and quite new with SQL coding too, so thanks for your help and your kind understanding of my "ignorance".
I am trying to link table 1 (1 column called client_name) to table 2 (1 column called advertiser_name) where table 2 contains a list of 5000 terms that could (or could not) be embedded into any of the client_names in table 1.

Here is an example

Table1
Client_name:
Walt Disney Corporation
Ebay Inc
.com Amazon account

Table2
Advertiser_name:
Amazon
Disney
Ebay

The output i need is a 3rd table that would look like this
Client_Name Advertiser_Name
Walt Disney Corporation Disney
Ebay Inc Ebay
.com Amazon account Amazon

Like %table2.advertiser_Name% would have been great but it seems the like operator only works with a constant.

Is there any solution to resolve this problem.

THANK you SO MUCH for your help. I much appreciate it!
Julie

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-09 : 22:16:33

Try this:


select t1.Client_name 
       ,t2.Advertiser_name
from   table2 t2
inner join table1 t1
       on t1.Client_name like '%' + t2.Advertiser_name + '%'

Be One with the Optimizer
TG

lichette
Starting Member

3 Posts

Posted - 2009-10-10 : 14:42:59

Thanks TG but it did not work. I got the following error messages:
ERROR: Expression using addition (+) requires numeric types.
ERROR: LIKE operator requires character operands

Is there another way to achieve this, since your proposed solution unfortunately did not work?

Thanks again for your suggestion.
Julie

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-10 : 17:13:26

Please post the code that you tried as well as the sql datatypes of the columns involved.

Be One with the Optimizer
TG

lichette
Starting Member

3 Posts

Posted - 2009-10-10 : 17:18:54

here is the code i tried (I'm using SAS not mySQL therefore the proc sql statement):
proc sql;
Create table temp as
select t1.Company_name
,t2.Advertiser
from SSP.SSPClients as t2
inner join SSP.Datamart as t1
on t1.Company_name like '%' + t2.Advertiser + '%';
quit;

Company_name and Advertiser are both text fields (length of $44 and $36)

Thanks again!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-10 : 17:28:08

Oh - sorry to waste your time, Julie. This forum is specific to MS Sql Server. I think you'll have to find a SAS forum...

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources