String replacement

[R Camarda]

Help, I have a vendor file with the vendors name. I need to add a
field that has alternate captions
eg. :
In my vendor file I have records for AT&T with three different
names/spellings for AT&T.

AT&T
Lucent Technologies
A T & T

I want to add a new field that would have AT&T for the above records.

I know I could hardcode a query
Update F_VENDOR
Set VENDOR_CAPTION_ALT = 'AT&T'
where VENDOR_CAPTION like '%Lucent%' or
VENDOR_CAPTION like '%A T & T%'

however, how can I use another table that has the my search keywords
for the "like" and the replacement captions so I dont need literal
queries to update all the diffent vendors?

I have identified 96 instances of where I want to have an alternative
name that I can use for a type of grouping. This gets really wild
with the military such as Army and Navy.
TIA
Rob

[Ellen K.]

For a longer-term solution, you need a vendor table and your
application should force the user to pick from a list populated by
that table, so that you don't get this problem in the future.

On 20 Jan 2004 06:23:44 -0800, rc******@cablespeed.com (R Camarda)
wrote:

Help, I have a vendor file with the vendors name. I need to add a
field that has alternate captions
eg. :
In my vendor file I have records for AT&T with three different
names/spellings for AT&T.

AT&T
Lucent Technologies
A T & T

I want to add a new field that would have AT&T for the above records.

I know I could hardcode a query
Update F_VENDOR
Set VENDOR_CAPTION_ALT = 'AT&T'
where VENDOR_CAPTION like '%Lucent%' or
VENDOR_CAPTION like '%A T & T%'

however, how can I use another table that has the my search keywords
for the "like" and the replacement captions so I dont need literal
queries to update all the diffent vendors?

I have identified 96 instances of where I want to have an alternative
name that I can use for a type of grouping. This gets really wild
with the military such as Army and Navy.
TIA
Rob

[Erland Sommarskog]

R Camarda (rc******@cablespeed.com) writes:

I want to add a new field that would have AT&T for the above records.

I know I could hardcode a query
Update F_VENDOR
Set VENDOR_CAPTION_ALT = 'AT&T'
where VENDOR_CAPTION like '%Lucent%' or
VENDOR_CAPTION like '%A T & T%'

however, how can I use another table that has the my search keywords
for the "like" and the replacement captions so I dont need literal
queries to update all the diffent vendors?

IF I understand this correctly:

UPDATE F_VENDOR
SET VENDOR_CAPTION_ALT = r.good_caption
FROM F_VENDOR f
JOIN replacements r ON f.VENDORC_CAPTION LIKE '%' + r.bad_caption + '%'
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[R Camarda]

Ellen K. <72************************@compuserve.com> wrote in message news:<74********************************@4ax.com>. ..

For a longer-term solution, you need a vendor table and your
application should force the user to pick from a list populated by
that table, so that you don't get this problem in the future.

Absolutely, However I don't have control over the source application
and I am trying to do data cleansing for a data warehouse.

Also, the solution will help expand my knowledge of SQL techniques
that, in turn, I may help someone else someday.

[R Camarda]

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Erland, that did the trick. Thank you very much!

Rob

IF I understand this correctly:

UPDATE F_VENDOR
SET VENDOR_CAPTION_ALT = r.good_caption
FROM F_VENDOR f
JOIN replacements r ON f.VENDORC_CAPTION LIKE '%' + r.bad_caption + '%'