Partial Match / Duplicates

Question

Post reply

Partial Match / Duplicates

venkyzrocks

SSCommitted

Points: 1907
More actions
April 21, 2014 at 2:31 pm

#306618

Hi,
I'm trying to figure out values that match partially or are duplicates, but it is supposed to be a row by row comparison.
Is there a way to do this. :
Region Partial
FY13 Alaska Yes
FY14 Alaska Yes
FY15 A No
Baltimore Yes
FY15 Baltimore Yes
Please delete the Partial column, the values shown are only for example. I need to insert these values if there is a partial match.
DDL Script below :
USE [Test]
GO
/****** Object: Table [dbo].[Region] Script Date: 04/21/2014 16:23:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Region](
[Region] [varchar](500) NOT NULL,
[Partial] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Region] ([Region], [Partial]) VALUES (N'FY13-Alaska', N'Yes')
INSERT [dbo].[Region] ([Region], [Partial]) VALUES (N'FY13-Arizona', N'Yes')
INSERT [dbo].[Region] ([Region], [Partial]) VALUES (N'FY13-Arkansas', N'Yes')
INSERT [dbo].[Region] ([Region], [Partial]) VALUES (N'FY13-California', N'Yes')
INSERT [dbo].[Region] ([Region], [Partial]) VALUES (N'FY13-Colorado', N'Yes')
INSERT [dbo].[Region] ([Region], [Partial]) VALUES (N'FY13-Connecticut', N'Yes')
INSERT [dbo].[Region] ([Region], [Partial]) VALUES (N'FY14-Alaska', N'Yes')
INSERT [dbo].[Region] ([Region], [Partial]) VALUES (N'FY14-Arizona', N'Yes')
INSERT [dbo].[Region] ([Region], [Partial]) VALUES (N'FY14-Arkansas', N'Yes')
INSERT [dbo].[Region] ([Region], [Partial]) VALUES (N'FY14-California', N'Yes')
INSERT [dbo].[Region] ([Region], [Partial]) VALUES (N'FY14-Colorado', N'Yes')
INSERT [dbo].[Region] ([Region], [Partial]) VALUES (N'FY14-Connecticut', N'Yes')
INSERT [dbo].[Region] ([Region], [Partial]) VALUES (N'FY15-Arizo', N'Yes')
INSERT [dbo].[Region] ([Region], [Partial]) VALUES (N'FY15-A', N'No')
INSERT [dbo].[Region] ([Region], [Partial]) VALUES (N'FY15-Baltimore', N'No')
INSERT [dbo].[Region] ([Region], [Partial]) VALUES (N'FY15_U', N'No')

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

Partial match to what?

do you have a master table for states, for example?

your data had some single letter possible matches... if you filtered for the letter "A" or "U", how do you know "A" is Alabama or Arizona?

"U" would be Utah, because it starts with u, not contains "u" like Connecticut or Kentucky, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

venkyzrocks SSCommitted Points: 1907 More actions · Answer 2

Hi ,

No master table exists. The temporary master table has been posted below. I'll probably create one. So stripping off the FY** values and create a master list and do a partial match.

Does this help. Here use this

select distinct RIGHT(Region, LEN(Region) -5) from Region

where LEN(RIGHT(Region, LEN(Region) -5)) > 5

Thanks.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

venkyzrocks (4/21/2014)
Hi ,
No master table exists. The temporary master table has been posted below. I'll probably create one. So stripping off the FY** values and create a master list and do a partial match.
Does this help. Here use this
select distinct RIGHT(Region, LEN(Region) -5) from Region
where LEN(RIGHT(Region, LEN(Region) -5)) > 5
Thanks.

Do you mean something along these lines:

SELECT *

FROM (

SELECT

R.Region

,ROW_NUMBER() OVER

(

PARTITION BY SUBSTRING(R.Region,CHARINDEX(NCHAR(45),R.Region,1)+1,LEN(R.Region))

ORDER BY (SELECT NULL)

) AS RID

,R.Partial

FROM dbo.Region R

) AS X WHERE X.RID = 1;

Results:

Region RID Partial

----------------- ---- --------

FY15-A 1 No

FY13-Alaska 1 Yes

FY15-Arizo 1 Yes

FY14-Arizona 1 Yes

FY13-Arkansas 1 Yes

FY15-Baltimore 1 No

FY14-California 1 Yes

FY13-Colorado 1 Yes

FY14-Connecticut 1 Yes

FY15_U 1 No

😎

venkyzrocks SSCommitted Points: 1907 More actions · Answer 4

I think this is a good solution. I can address the single character matches manually if i have to . Thanks.