Using a look up table to remove values in another using pattern matching

Question

Post reply

Using a look up table to remove values in another using pattern matching

Weegee2017

SSC Eights!

Points: 994
More actions
July 6, 2018 at 8:03 am

#358189

Hi there
I have a table of data that I need to data cleanse which is called #SampleData
It contains a set of entries that i need to set to NULL based on looking up values in another table #HoldingDesignationExclusion
My table data is shown below
-- DROP TEMP TABLE IF ALREADY EXISTS
If OBJECT_ID(N'tempdb..#HoldingDesignationExclusion', N'U') IS NOT NULL DROP TABLE #HoldingDesignationExclusion
-- DROP TEMP TABLE IF ALREADY EXISTS
If OBJECT_ID(N'tempdb..#SampleData', N'U') IS NOT NULL DROP TABLE #SampleData
CREATE TABLE [#HoldingDesignationExclusion](
[Exclusion] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT [#HoldingDesignationExclusion]([Exclusion]) VALUES (N'EXECUTORS')
GO
INSERT [#HoldingDesignationExclusion]([Exclusion]) VALUES (N'EXECUTRIX')
GO
INSERT [#HoldingDesignationExclusion]([Exclusion]) VALUES (N'CURATOR')
GO
INSERT [#HoldingDesignationExclusion]([Exclusion]) VALUES (N'EXECUTRICES')
GO
CREATE TABLE [#SampleData](
[Personid] [float] NULL,
[Length] [float] NULL,
[holdingdesignationname] [nvarchar](255) NULL,
[DerivedPayeeName] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT [#SampleData]([Personid], [Length], [holdingdesignationname], [DerivedPayeeName]) VALUES (1112032632, 56, N'EXECUTORS OF THE LATE MRS DAVID BOWMAN', N'NULL')
GO
INSERT [#SampleData] ([Personid], [Length], [holdingdesignationname], [DerivedPayeeName]) VALUES (1112034242, 70, N'EXECUTRIX OF THE LATE DAVID JONES W/W EXECUTOR OF THE LATE J', N'NULL')
GO
INSERT [#SampleData] ([Personid], [Length], [holdingdesignationname], [DerivedPayeeName]) VALUES (1112035829, 55, N'ADMINISTRATORS OF THE LATE MRS JANE DOE', N'NULL')
GO
INSERT [#SampleData] ([Personid], [Length], [holdingdesignationname], [DerivedPayeeName]) VALUES (1112034242, 70, N'EXECUTRIX OF THE LATE JOHN JONES W/W EXECUTOR OF THE LATE J', N'NULL')
GO
how can i use the value in Exclusion in the table [#HoldingDesignationExclusion] to set the values for [holdingdesignationname] in #SampleData?
And can i use a join to match data Something like
Select *
from [#SampleData] a
join [#HoldingDesignationExclusion] b on a.holdingdesignationname = '%' + b.Exclusion + '%'

Viewing 5 posts - 1 through 4 (of 4 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Yes, you can do that, but you have to use "LIKE" instead of "=". What are your expected results?

John

Weegee2017 SSC Eights! Points: 994 More actions · Answer 2

John Mitchell-245523 - Friday, July 6, 2018 8:17 AM
Yes, you can do that, but you have to use "LIKE" instead of "=". What are your expected results?
John

Hi John

Thanks for that.

Ok i want to do somthing like this

Update a

set a.holdingdesignationname = NULL

from [#SampleData] a

join [#HoldingDesignationExclusion] b on a.holdingdesignationname like ('%' + b.Exclusion + '%')

select * from [#SampleData] a

So using LIKE works

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 3

depending on how the data looks, if its guaranteed to the exclusion is guaranteed to be the fisrt word in the string and is separate you can use substring with patindex or charindex like below

Select *

from [#SampleData] a

join [#HoldingDesignationExclusion] b on b.Exclusion =substring(a.holdingdesignationname,0, patindex ('%[ ]%',a.holdingdesignationname))

***The first step is always the hardest *******

Weegee2017 SSC Eights! Points: 994 More actions · Answer 4

SGT_squeequal - Friday, July 6, 2018 8:26 AM
depending on how the data looks, if its guaranteed to the exclusion is guaranteed to be the fisrt word in the string and is separate you can use substring with patindex or charindex like below
Select *
from [#SampleData] a
join [#HoldingDesignationExclusion] b on b.Exclusion =substring(a.holdingdesignationname,0, patindex ('%[ ]%',a.holdingdesignationname))

The Exclusion could be the first word in the string but it may not be and i would have search the whole string in SampleData for it