February 19, 2018 at 9:43 am
Hi I have one doubt in sql server .
how to updated target table flag using source table flag in sql server based on id and address columns.
here when comparing id and address time(souce and target tables) we need to consider onley character and numbers data only.
while updateing time onlye cosider 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 |
Here I want updated target tables Flag using source table id + address
source table addres 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 addres and target table address are same when we are not considering spaces and special character and address is 116ESpenceStB
table 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
substring (target.address, charindex( ' ', target.address,1),len(target.address))
=substring (source.address, charindex( ' ', source.address,1),len(source.address))
above query not give expected result .
please tell me how to write query to achive this task in sql server .
February 20, 2018 at 5:16 am
Is your server/database or the column case sensitive or not?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2018 at 7:54 am
You could try the following.
What i do is recursively look for patterns(patindex(%[^a-z0-9]%)) which is non alphanumeric and replace it with null as you can see in the source_data and dest_data.
After that i join on the scrubbed_values of source_data and dest_data and MERGE it with the targetemp based on the matched address.
with source_data(str1,pat_val1,scrubbed_val1,flag1)
as (select address as str
,patindex('%[^a-z0-9]%',address) as pat_val
,cast(stuff(address
,patindex('%[^a-z0-9]%',address)
,1,'') as varchar(1000))as scrubed_val
,flag
from [sourcemp]
union all
select str1
,patindex('%[^a-z0-9]%',scrubbed_val1)
,case when patindex('%[^a-z0-9]%',scrubbed_val1)=0 then
scrubbed_val1
else cast(stuff(scrubbed_val1
,patindex('%[^a-z0-9]%',scrubbed_val1)
,1,'') as varchar(1000))
end
,flag1
from source_data
where pat_val1<>0
)
,dest_data(str1,pat_val1,scrubbed_val1)
as(select address as str
,patindex('%[^a-z0-9]%',address) as pat_val
,cast(stuff(address
,patindex('%[^a-z0-9]%',address)
,1,'') as varchar(1000))as scrubed_val
from [targetemp]
union all
select str1
,patindex('%[^a-z0-9]%',scrubbed_val1)
,case when patindex('%[^a-z0-9]%',scrubbed_val1)=0 then
scrubbed_val1
else cast(stuff(scrubbed_val1
,patindex('%[^a-z0-9]%',scrubbed_val1)
,1,'') as varchar(1000))
end
from dest_data
where pat_val1<>0)
merge into [targetemp] x
using ( select a.str1,b.flag1
from dest_data a
join source_data b
on a.scrubbed_val1=b.scrubbed_val1
and a.pat_val1=0
and b.pat_val1=0
)y
ON x.address=y.str1
when matched then
update
set x.flag=y.flag1;
February 20, 2018 at 9:27 am
george-178499 - Tuesday, February 20, 2018 7:54 AMYou could try the following.
What i do is recursively look for patterns(patindex(%[^a-z0-9]%)) which is non alphanumeric and replace it with null as you can see in the source_data and dest_data.After that i join on the scrubbed_values of source_data and dest_data and MERGE it with the targetemp based on the matched address.
with source_data(str1,pat_val1,scrubbed_val1,flag1)
as (select address as str
,patindex('%[^a-z0-9]%',address) as pat_val
,cast(stuff(address
,patindex('%[^a-z0-9]%',address)
,1,'') as varchar(1000))as scrubed_val
,flag
from [sourcemp]
union all
select str1
,patindex('%[^a-z0-9]%',scrubbed_val1)
,case when patindex('%[^a-z0-9]%',scrubbed_val1)=0 then
scrubbed_val1
else cast(stuff(scrubbed_val1
,patindex('%[^a-z0-9]%',scrubbed_val1)
,1,'') as varchar(1000))
end
,flag1
from source_data
where pat_val1<>0
)
,dest_data(str1,pat_val1,scrubbed_val1)
as(select address as str
,patindex('%[^a-z0-9]%',address) as pat_val
,cast(stuff(address
,patindex('%[^a-z0-9]%',address)
,1,'') as varchar(1000))as scrubed_val
from [targetemp]
union all
select str1
,patindex('%[^a-z0-9]%',scrubbed_val1)
,case when patindex('%[^a-z0-9]%',scrubbed_val1)=0 then
scrubbed_val1
else cast(stuff(scrubbed_val1
,patindex('%[^a-z0-9]%',scrubbed_val1)
,1,'') as varchar(1000))
end
from dest_data
where pat_val1<>0)
merge into [targetemp] x
using ( select a.str1,b.flag1
from dest_data a
join source_data b
on a.scrubbed_val1=b.scrubbed_val1
and a.pat_val1=0
and b.pat_val1=0
)y
ON x.address=y.str1
when matched then
update
set x.flag=y.flag1;
Right idea but I'd really avoid the recursive CTE. Comparatively horrible performance to be had there especially since you have to recalculate every time you do a query.
I still need to know from the OP if case sensitivity comes into play for any of this.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply