How to avoid special characters and spaces when update in SQL Server

Question

How to avoid special characters and spaces when update in SQL Server

asrinu13

Say Hey Kid

Points: 709
More actions
March 16, 2018 at 1:34 am

#410326

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
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.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

The reason it's taking so long is because using REPLACE is making the query non-SARGable, thus the (poor) data engine is having to scan every row, and applying the REPLACE to it to check if it's the row it wants. With a decent amount of data, and that many REPLACE's, that is going to take a long time; and there's no way around that.

If you need a to be able to query the address without the spaces and special characters, add a PERSISTED computed column to your table, and add an INDEX to it. This should get you started:

CREATE TABLE #Address (ID int IDENTITY(1,1), Address1 varchar(50)); GO INSERT INTO #Address (Address1) VALUES ('700 N. C Apt# J1w02'),('9717 E. 6TH AE #32'); GO SELECT * FROM #Address; GO ALTER TABLE #Address ADD Address1_NS AS 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(Address1,' ',''),'~',''),'`',''),'!',''),'@',''),'!',''),'#',''),'$',''),'%','') ,'^',''),'&',''),'*',''),'(',''),')',''),'-',''),'_',''),'=',''),'+',''),',',''),'.',''),'/',''),'\',''),'<',''),'>',''),'?',''),'"',''),'''',''),':',''),';',''),'{',''),'}',''),'[',''),']',''),'\',''),'|','') PERSISTED; GO

SELECT * FROM #Address; GO DROP TABLE #Address

Note that adding that column to your table is probably going to take a (very) long time, and you'll need to do the process on BOTH tables. After it's done, however, you'll be able to make use of it and notice a much better performance.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk