使用UPDATE顺序缩写地址信息 [英] Using UPDATE to sequentially abbreviate address information
问题描述
问候，
我正在尝试使用标准更新地址字段缩写如此
我可以在
地址上对各个帐户进行比较。我可以为道路更新一组记录。到Rd，但是当我试图堆叠更新条款时，我似乎在
文件中得到随机更新。所有更新都是正确的，但它们不完整。不是
确定如何做到这一点，我添加了一个TOP声明，但是
不起作用。有没有办法简单地将这些串在一起
查询？
基本思路是创建新地址，address_line_1_fix，
，同时保留原始地址address_line_1，原封不动。
UPDATE TOP（100）PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE（address_line_1，''Road''，''Rd''）
WHERE address_line_1喜欢''％Road％''
GO
UPDATE TOP（100）PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE（address_line_1，''Avenue''，''Ave''）
WHERE address_line_1 like' '％Avenue％''
GO
UPDATE TOP（100）PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE（address_line_1，''Street ''，''St''）
WHERE address_line_1喜欢''％Street％''
GO
UPDATE TOP（100）PERCENT D b o.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE（address_line_1，''Boulevard''，''Blvd''）
WHERE address_line_1喜欢''％Boulevard％''
GO
Greetings,
I''m trying to update an address field with "standard" abbreviations so
that I can do a comparison of various accounts to one another on the
address. I can update a set of records for "Road" to "Rd", but when I
tried to stack the update clauses, I seem to get random updates within
the file. All the updates are correct, but they''re incomplete. Not
sure how this needs to be done, I added a TOP statement but that
didn''t work. Is there way to simply string these together in a single
query?
The basic idea is to create the new address, "address_line_1_fix",
while leaving the original address, "address_line_1", intact.
UPDATE TOP (100) PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE(address_line_1,''Road'',''Rd'')
WHERE address_line_1 like ''%Road%''
GO
UPDATE TOP (100) PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE(address_line_1,''Avenue'',''Ave'')
WHERE address_line_1 like ''%Avenue%''
GO
UPDATE TOP (100) PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE(address_line_1,''Street'',''St'')
WHERE address_line_1 like ''%Street%''
GO
UPDATE TOP (100) PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE(address_line_1,''Boulevard'',''Blvd'')
WHERE address_line_1 like ''%Boulevard%''
GO
推荐答案
首先摆脱TOP（100）PERCENT废话。虽然它b / b
应该没有任何效果，但它没有用处，只会让事情混淆。
其次，如果你说的不是你想要的所有行更新
更新，将您的UPDATE命令转换为查询，看看返回的是什么是
。如果SELECT使用给定的WHERE子句返回行，则
具有相同WHERE子句的UPDATE应更新相同的行。还要
仔细查看文字的拼写;在WHERE子句中使用不同的拼写
''Boulevard''和SET子句无法正常工作。
如果你想在一个单独的地方做这个查询你需要嵌套REPLACE
函数和OR测试。
UPDATE dbo.All_Client_Companies_For_Fix
SET address_line_1_fix =
替换（
替换（
替换（
REPLACE（address_line_1，
' 'Boulevard''，''Blvd''），
''街''，''St''），
''Avenue''，''Ave ''），
''Road''，''Rd''）
WHERE（address_line_1喜欢''％Road％''
或者address_line_1喜欢''％Avenue％''
或者address_line_1喜欢''％Street％''
或者address_line_1喜欢''％Boulevard％''）
Roy Harvey
Beacon Falls，CT
2008年7月2日星期三12:26:57 -0700（ PDT），Chris H
< c h********@broadreachpartnersinc.comwrote：
First of all get rid of the TOP (100) PERCENT nonsense. While it
should have no effect, it serves no purpose and just confuses things.
Second, if you are saying that not all rows you expect to be updated
are updated, turn your UPDATE commands into queries and see what is
returned. If the SELECT returns rows using a given WHERE clause, then
an UPDATE with the same WHERE clause should update the same rows. Also
double check the spelling of the literals; a different spelling of
''Boulevard'' in the WHERE clause and SET clause would not work right.
If you want to do this in a single query you need to nest the REPLACE
functions, and OR the tests.
UPDATE dbo.All_Client_Companies_For_Fix
SET address_line_1_fix =
REPLACE(
REPLACE(
REPLACE(
REPLACE(address_line_1,
''Boulevard'',''Blvd''),
''Street'',''St''),
''Avenue'',''Ave''),
''Road'',''Rd'')
WHERE (address_line_1 like ''%Road%''
OR address_line_1 like ''%Avenue%''
OR address_line_1 like ''%Street%''
OR address_line_1 like ''%Boulevard%'')
Roy Harvey
Beacon Falls, CT
On Wed, 2 Jul 2008 12:26:57 -0700 (PDT), Chris H
<ch********@broadreachpartnersinc.comwrote:
>问候，
我正在尝试更新具有标准的地址字段缩写如此
我可以在
地址上相互比较各种帐户。我可以为道路更新一组记录。到Rd，但是当我试图堆叠更新条款时，我似乎在文件中得到了随机更新。所有更新都是正确的，但它们不完整。不确定如何做到这一点，我添加了一个TOP声明，但是
没有用。有没有办法简单地在一个
查询中将它们串在一起？
基本的想法是创建新地址，address_line_1_fix，
，同时保留原始地址， address_line_1，完好无损。
UPDATE TOP（100）PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE（address_line_1，''Road''，''Rd''）
WHERE address_line_1喜欢''％Road％''
GO
UPDATE TOP（100）PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE（address_line_1，''Avenue''，''Ave' '）
WHERE address_line_1喜欢''％Avenue％''
GO
UPDATE TOP（100）PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE（address_line_1，''Street'' ，''St''）
WHERE address_line_1喜欢''％Street％''
GO
UPDATE TOP（100）PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE（address_line_1， '' Boulevar d''，''Blvd''）
地址_line_1喜欢''％Boulevard％''
GO
>Greetings,
I''m trying to update an address field with "standard" abbreviations so
that I can do a comparison of various accounts to one another on the
address. I can update a set of records for "Road" to "Rd", but when I
tried to stack the update clauses, I seem to get random updates within
the file. All the updates are correct, but they''re incomplete. Not
sure how this needs to be done, I added a TOP statement but that
didn''t work. Is there way to simply string these together in a single
query?
The basic idea is to create the new address, "address_line_1_fix",
while leaving the original address, "address_line_1", intact.
UPDATE TOP (100) PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE(address_line_1,''Road'',''Rd'')
WHERE address_line_1 like ''%Road%''
GO
UPDATE TOP (100) PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE(address_line_1,''Avenue'',''Ave'')
WHERE address_line_1 like ''%Avenue%''
GO
UPDATE TOP (100) PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE(address_line_1,''Street'',''St'')
WHERE address_line_1 like ''%Street%''
GO
UPDATE TOP (100) PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE(address_line_1,''Boulevard'',''Blvd'')
WHERE address_line_1 like ''%Boulevard%''
GO
有来自Melissa Data and Group的数据清理产品
将为您提供更多功能。不要重新发明
轮。
There are address data scrubbing products from Melissa Data and Group
One which will do this for you and a lot more. Do not re-invent the
wheel.
请删除愚蠢的TOP 100％。
替换的潜在问题是您没有使用任何
分隔符。如果您的地址为Broadway，则Road部分
将替换为Rd。导致BRdway。
因此，您需要弄清楚如何正确替换任何单个
术语，例如通过添加前缀和/或后缀
搜索字词和替换字词的空格。
一旦这是正确的，你可以简单地将几个替换嵌入一个
UPDATE语句。这样的事情：
UPDATE dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE（替换（
address_line_1，''Road'' ，''Rd''）
，''街''，''St''）
WHERE address_line_1 LIKE''％Road％''
或者address_line_1 LIKE''％Street％''
-
Gert-Jan
SQL Server MVP
Chris H写道：
Please remove the silly TOP 100 PERCENT.
A potential problem with your replaces is that you are not using any
delimiter. If you address line reads "Broadway", then the "Road"-part
will be replaced with "Rd" resulting in "BRdway".
So you will need to figure out how to properly replace any individual
term, for example by prefixing and/or postfixing a space to both the
search term and the replacement term.
Once that is correct, you can simply nest several replacements into one
UPDATE statement. Something like this:
UPDATE dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE(REPLACE(
address_line_1, ''Road'', ''Rd'')
, ''Street'', ''St'')
WHERE address_line_1 LIKE ''%Road%''
OR address_line_1 LIKE ''%Street%''
--
Gert-Jan
SQL Server MVP
Chris H wrote:
>
问候，
我正在尝试使用标准更新地址字段缩写如此
我可以在
地址上对各个帐户进行比较。我可以为道路更新一组记录。到Rd，但是当我试图堆叠更新条款时，我似乎在
文件中得到随机更新。所有更新都是正确的，但它们不完整。不是
确定如何做到这一点，我添加了一个TOP声明，但是
不起作用。有没有办法简单地将这些串在一起
查询？
基本思路是创建新地址，address_line_1_fix，
，同时保留原始地址address_line_1，原封不动。
UPDATE TOP（100）PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE（address_line_1，''Road''，''Rd''）
WHERE address_line_1喜欢''％Road％''
GO
UPDATE TOP（100）PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE（address_line_1，''Avenue''，''Ave''）
WHERE address_line_1 like' '％Avenue％''
GO
UPDATE TOP（100）PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE（address_line_1，''Street ''，''St''）
WHERE address_line_1喜欢''％Street％''
GO
UPDATE TOP（100）PERCENT DBO。 All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE（address_line_1，''Boulevard''，''Blvd''）
WHERE address_line_1喜欢''％Boulevard％''
GO
>
Greetings,
I''m trying to update an address field with "standard" abbreviations so
that I can do a comparison of various accounts to one another on the
address. I can update a set of records for "Road" to "Rd", but when I
tried to stack the update clauses, I seem to get random updates within
the file. All the updates are correct, but they''re incomplete. Not
sure how this needs to be done, I added a TOP statement but that
didn''t work. Is there way to simply string these together in a single
query?
The basic idea is to create the new address, "address_line_1_fix",
while leaving the original address, "address_line_1", intact.
UPDATE TOP (100) PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE(address_line_1,''Road'',''Rd'')
WHERE address_line_1 like ''%Road%''
GO
UPDATE TOP (100) PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE(address_line_1,''Avenue'',''Ave'')
WHERE address_line_1 like ''%Avenue%''
GO
UPDATE TOP (100) PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE(address_line_1,''Street'',''St'')
WHERE address_line_1 like ''%Street%''
GO
UPDATE TOP (100) PERCENT dbo.All_Client_Companies_For_Fix
SET address_line_1_fix = REPLACE(address_line_1,''Boulevard'',''Blvd'')
WHERE address_line_1 like ''%Boulevard%''
GO
这篇关于使用UPDATE顺序缩写地址信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
