Select query taking more time in sql server

Transact-SQL
1

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.

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 NULL,
[Flag] [int] NULL
)

----Target table: we need update flag value using source table
CREATE TABLE [dbo].[targetemp]
(
[id] [int] NULL,
[address] varchar 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 NULL,
[Flag] [int] NULL
)

----Target table: we need update flag value using source table
CREATE TABLE [dbo].[targetemp]
(
[id] [int] NULL,
[address] varchar 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. AtlanticSTE 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.

2

Try this (in test environment)

Update 
update a
   set a.flag=b.flag
  from dbo.targetemp as a
       inner join dbo.sourcemp as b
               on b.id=a.id
              and replace(translate(b.[address],char(10)+char(13)+'~!@#$%^&*()-_=+,./\<>?"'':;{}[]|','                                 '),' ','')
                 =replace(translate(a.[address],char(10)+char(13)+'~!@#$%^&*()-_=+,./\<>?"'':;{}[]|','                                 '),' ','')
;