How to avoid special characters and spaces when update in SQL Server I have a question about SQL Server: how to update target table flag using source table flag in SQL Server based on id and address columns.
如何在SQL Server中更新时避免使用特殊字符和空格我对SQL Server有疑问:如何使用基于id和address列的SQL Server中的源表标志更新目标表标志。
When comparing id and address time (source and target tables), we need to consider only character and numbers data only.
在比较id和地址时间(源表和目标表)时,我们只需要考虑字符和数字数据。
While updating time, only consider characters and numbers only no need to consider any spaces or special characters.
在更新时间时,只考虑字符和数字,不需要考虑任何空格或特殊字符。
Example: source table :
示例:源表:
id | address | Flag
1 | 700 N. C Apt J1w02 | 1
Target table :
目标表:
id | address | Flag
1 | 700 N. C Apt J1w02 |
I want to update target table's Flag using source table id + address.
我想使用源表id +地址更新目标表的Flag。
Source table address and target table address are same when we are not considering spaces and special character and address is 700NCAptJ1w02 so Flag will be updated in target table Flag is :1 similar to others
当我们不考虑空格和特殊字符和地址是700NCAptJ1w02时,源表地址和目标表地址是相同的所以标志将在目标表中更新标志是:1类似于其他
Output is : target table :
输出为:目标表:
id | address | Flag
1 | 700 N. C Apt J1w02 | 1
in target table we need to updated only Flag column only.
在目标表中,我们只需要更新Flag列。
Another example:
Source table :
来源表:
id | address | Flag
4 | 116 E Spence St #B | 0
Target table :
目标表:
id | address | Flag
4 | 11 6 E Sp enc e St #B NULL |
Source table address and target table address are same when we are not considering spaces and special character and address is 116ESpenceStB
当我们不考虑空格和特殊字符和地址是116ESpenceStB时,源表地址和目标表地址是相同的
Table output record is :
表输出记录是:
id | address | Flag
4 | 11 6 E Sp enc e St #B NULL | 0
Sample table data with script is :
使用脚本的示例表数据是:
---source table :
CREATE TABLE [dbo].[sourcemp]
(
[id] [int] NULL,
[address] [varchar](200) NULL,
[Flag] [int] NULL
)
----Target table: we need update flag value using source table
CREATE TABLE [dbo].[targetemp]
(
[id] [int] NULL,
[address] [varchar](200) NULL,
[Flag] [int] NULL
)
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (1, N'700 N. C Apt# J1w02', 1)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (1, N'7010 N COLTON', 0)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (1, N'0923 E 55th ten-332', 0)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (1, N'9717 E. 6TH AE #32', 0)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (2, N'5704 E Chattaroy Rd', 1)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (2, N'hen@ye yte&t#100', 0)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (2, N'2903 E. Euclid, Apt. #40', 3)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (3, N'327 1/2 W. 2nd Ave RM SP3', 1)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (3, N'c/o DC!FS 1313 N. Atl*(antic STE 2000', 2)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (4, N'2706 W. College Ave.', 1)
GO
I have a question about SQL Server: how to update target table flag using source table flag in SQL Server based on id and address columns.
When comparing id and address time (source and target tables), we need to consider only character and numbers data only.
While updating time, only consider characters and numbers only no need to consider any spaces or special characters.
Example: source table :
id | address | Flag
1 | 700 N. C Apt J1w02 | 1
Target table :
id | address | Flag
1 | 700 N. C Apt J1w02 |
I want to update target table's Flag using source table id + address.
Source table address and target table address are same when we are not considering spaces and special character and address is 700NCAptJ1w02 so Flag will be updated in target table Flag is :1 similar to others
Output is : target table :
id | address | Flag
1 | 700 N. C Apt J1w02 | 1
in target table we need to updated only Flag column only.
Another example:
Source table :
id | address | Flag
4 | 116 E Spence St #B | 0
Target table :
id | address | Flag
4 | 11 6 E Sp enc e St #B NULL |
Source table address and target table address are same when we are not considering spaces and special character and address is 116ESpenceStB
Table output record is :
id | address | Flag
4 | 11 6 E Sp enc e St #B NULL | 0
Sample table data with script is :
---source table :
CREATE TABLE [dbo].[sourcemp]
(
[id] [int] NULL,
[address] [varchar](200) NULL,
[Flag] [int] NULL
)
----Target table: we need update flag value using source table
CREATE TABLE [dbo].[targetemp]
(
[id] [int] NULL,
[address] [varchar](200) NULL,
[Flag] [int] NULL
)
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (1, N'700 N. C Apt# J1w02', 1)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (1, N'7010 N COLTON', 0)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (1, N'0923 E 55th ten-332', 0)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (1, N'9717 E. 6TH AE #32', 0)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (2, N'5704 E Chattaroy Rd', 1)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (2, N'hen@ye yte&t#100', 0)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (2, N'2903 E. Euclid, Apt. #40', 3)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (3, N'327 1/2 W. 2nd Ave RM SP3', 1)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (3, N'c/o DC!FS 1313 N. Atl*(antic STE 2000', 2)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (4, N'2706 W. College Ave.', 1)
GO
INSERT [dbo].[sourcemp] ([id], [address], [Flag]) VALUES (4, N'116 E Spence St #B', 0)
GO
INSERT [dbo].[targetemp] ([id], [address], [Flag]) VALUES (1, N'700 N. C Apt J1w02', NULL)
GO
INSERT [dbo].[targetemp] ([id], [address], [Flag]) VALUES (1, N'7010 N COLTON.', NULL)
GO
INSERT [dbo].[targetemp] ([id], [address], [Flag]) VALUES (1, N'0923 E 55th ten-332', NULL)
GO
INSERT [dbo].[targetemp] ([id], [address], [Flag]) VALUES (1, N'971%7 E. 6TH AE #32', NULL)
GO
INSERT [dbo].[targetemp] ([id], [address], [Flag]) VALUES (2, N'5704 E Chattaroy Rd', NULL)
GO
INSERT [dbo].[targetemp] ([id], [address], [Flag]) VALUES (2, N'henye yte&t100', NULL)
GO
INSERT [dbo].[targetemp] ([id], [address], [Flag]) VALUES (2, N'2903 E. !Euclid, Apt. #40', NULL)
GO
INSERT [dbo].[targetemp] ([id], [address], [Flag]) VALUES (3, N'327 1/2 W. 2nd Ave RM SP3', NULL)
GO
INSERT [dbo].[targetemp] ([id], [address], [Flag]) VALUES (3, N'c/o DC!FS 1313 N. Atl*anticSTE 2000', NULL)
GO
INSERT [dbo].[targetemp] ([id], [address], [Flag]) VALUES (4, N'2706 WCollege Ave.', NULL)
GO
INSERT [dbo].[targetemp] ([id], [address], [Flag]) VALUES (4, N'11 6 E Sp enc e St #B', NULL)
GO
based on above data I want output like below :
基于以上数据我想要输出如下:
id |address Flag
1 |700 N. C Apt J1w02 | 1
1 |7010 N COLTON. |0
1 |0923 E 55th ten-332 |0
1 |971%7 E. 6TH AE #32 |0
2 |5704 E Chattaroy Rd |1
2 |henye yte&t100 |0
2 |2903 E. !Euclid, Apt. #40 |3
3 |327 1/2 W. 2nd Ave RM SP3 |1
3 |c/o DC!FS 1313 N. Atl*anticSTE 2000 |2
4 |2706 WCollege Ave. |1
4 |11 6 E Sp enc e St #B |0
I tried like below
我尝试过如下
update target set target.flag=source.flag
from targetemp target join sourcemp source
on target.id=source.id
and
--and
replace ( replace ( replace ( replace (
replace ( replace ( replace ( replace ( replace ( replace ( replace
( replace ( replace ( replace ( replace ( replace ( replace ( replace (
replace ( replace ( replace ( replace ( replace ( replace ( replace ( replace
( replace( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(source.address,' ',''),'~',''),'`',''),'!',''),'@',''),'!',''),'#',''),'$','')
,'%','') ,'^',''),'&',''),'*',''),'(',''),')',''),'-',''),'_',''),'=',''),'+','')
,
',',''),'.',''),'/',''),'\',''),'<',''),'>',''),'?',''),'"',''),'''',''),':',''),';','')
,'{',''),'}',''),'[',''),']',''),'\',''),'|','')=
replace ( replace ( replace ( replace (
replace ( replace ( replace ( replace ( replace ( replace ( replace
( replace ( replace ( replace ( replace ( replace ( replace ( replace (
replace ( replace ( replace ( replace ( replace ( replace ( replace ( replace
( replace( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(target.address,' ',''),'~',''),'`',''),'!',''),'@',''),'!',''),'#',''),'$','')
,'%','') ,'^',''),'&',''),'*',''),'(',''),')',''),'-',''),'_',''),'=',''),'+','')
,
',',''),'.',''),'/',''),'\',''),'<',''),'>',''),'?',''),'"',''),'''',''),':',''),';','')
,'{',''),'}',''),'[',''),']',''),'\',''),'|','')
Above query is taking tooo long since 11 hours still is running. Please tell me how to write the query to achieve this task in SQL Server.
由于11小时仍在运行,因此上述查询耗时太长。请告诉我如何编写查询以在SQL Server中实现此任务。
1 个解决方案
#1
0
Here's one of easiest approaches. But performance will depend on your tables sizes and length of address column. Another option is to use tally table.
这是最简单的方法之一。但性能取决于您的表大小和地址列的长度。另一个选择是使用计数表。
with rcte as (
select
id, address, flag, ln = len(address), step = 1
, res = cast(case when substring(address, 1, 1) like '[0-9a-z]' then substring(address, 1, 1) else '' end as varchar(200))
from
sourcemp
union all
select
id, address, flag, ln, step + 1
, cast(res + case when substring(address, step + 1, 1) like '[0-9a-z]' then substring(address, step + 1, 1) else '' end as varchar(200))
from
rcte
where
step < ln
)
, rcte2 as (
select
id, address, ln = len(address), step = 1
, res = cast(case when substring(address, 1, 1) like '[0-9a-z]' then substring(address, 1, 1) else '' end as varchar(200))
from
targetemp
union all
select
id, address, ln, step + 1
, cast(res + case when substring(address, step + 1, 1) like '[0-9a-z]' then substring(address, step + 1, 1) else '' end as varchar(200))
from
rcte2
where
step < ln
)
, cte as (
select
a.id, a.address, b.Flag
from
rcte2 a
join rcte b on a.id = b.id and a.res = b.res
where
a.ln = a.step
and b.ln = b.step
)
update a
set
a.Flag = b.Flag
from
targetemp a
join cte b on a.id = b.id and a.address = b.address
Output:
id addresss flag
-------------------------------------------------
1 700 N. C Apt J1w02 1
1 7010 N COLTON. 0
1 0923 E 55th ten-332 0
1 971%7 E. 6TH AE #32 0
2 5704 E Chattaroy Rd 1
2 henye yte&t100 0
2 2903 E. !Euclid, Apt. #40 3
3 327 1/2 W. 2nd Ave RM SP3 1
3 c/o DC!FS 1313 N. Atl*anticSTE 2000 2
4 2706 WCollege Ave. 1
4 11 6 E Sp enc e St #B 0
#1
0
Here's one of easiest approaches. But performance will depend on your tables sizes and length of address column. Another option is to use tally table.
这是最简单的方法之一。但性能取决于您的表大小和地址列的长度。另一个选择是使用计数表。
with rcte as (
select
id, address, flag, ln = len(address), step = 1
, res = cast(case when substring(address, 1, 1) like '[0-9a-z]' then substring(address, 1, 1) else '' end as varchar(200))
from
sourcemp
union all
select
id, address, flag, ln, step + 1
, cast(res + case when substring(address, step + 1, 1) like '[0-9a-z]' then substring(address, step + 1, 1) else '' end as varchar(200))
from
rcte
where
step < ln
)
, rcte2 as (
select
id, address, ln = len(address), step = 1
, res = cast(case when substring(address, 1, 1) like '[0-9a-z]' then substring(address, 1, 1) else '' end as varchar(200))
from
targetemp
union all
select
id, address, ln, step + 1
, cast(res + case when substring(address, step + 1, 1) like '[0-9a-z]' then substring(address, step + 1, 1) else '' end as varchar(200))
from
rcte2
where
step < ln
)
, cte as (
select
a.id, a.address, b.Flag
from
rcte2 a
join rcte b on a.id = b.id and a.res = b.res
where
a.ln = a.step
and b.ln = b.step
)
update a
set
a.Flag = b.Flag
from
targetemp a
join cte b on a.id = b.id and a.address = b.address
Output:
id addresss flag
-------------------------------------------------
1 700 N. C Apt J1w02 1
1 7010 N COLTON. 0
1 0923 E 55th ten-332 0
1 971%7 E. 6TH AE #32 0
2 5704 E Chattaroy Rd 1
2 henye yte&t100 0
2 2903 E. !Euclid, Apt. #40 3
3 327 1/2 W. 2nd Ave RM SP3 1
3 c/o DC!FS 1313 N. Atl*anticSTE 2000 2
4 2706 WCollege Ave. 1
4 11 6 E Sp enc e St #B 0