We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

How to Find-out duplicate contact numbers (in a field) RSS

6 replies

Last post Feb 16, 2019 12:02 PM by yogyogi

‹ Previous Thread|Next Thread ›

Shortcuts

ayyappan.CNN

Member

364 Points

536 Posts

How to Find-out duplicate contact numbers (in a field)

Feb 12, 2019 07:30 AM|ayyappan.CNN|LINK

Dear Friends,

Pls help me to solve this, I want to find-out duplicate contact (in a field) number and want to fix

This is my Table: 

CREATE TABLE [dbo].[#StudentMaster](
	[StudentID] [int] not NULL,
	[StudentName] [varchar](70) NOT NULL,
	[Student_ContNo] [varchar](10) NULL
	) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[Student_ContNo]) VALUES(101, N'Ramki','9141234561')
INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[Student_ContNo]) VALUES(102, N'Krishna','9999999999')
INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[Student_ContNo]) VALUES(103, N'Ganesha','8888888888')
INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[Student_ContNo]) VALUES(104, N'Mani','1111111111')
INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[Student_ContNo]) VALUES(105, N'Rani','1234567890')

Expected Result is..

StudentID	StudentName	Student_ContNo	Result
101	Ramki	9141434561	Correct No.
102	Krishna	9999999999	Wrong No
103	Ganesha	8888888888	Wrong No
104	Mani	1111111111	Wrong No
105	Rani	1234567890	Wrong No

Thanks in advance.

Ayyappan.cn,
Dept of ICT.

Mark post(s) as "Answer" that helped you

- Reply
ayyappan.CNN
Member

364 Points

536 Posts

Re: How to Find-out duplicate contact numbers (in a field)

Feb 12, 2019 07:58 AM|ayyappan.CNN|LINK

Hi Friends,

I try this, please check and share your views.

SELECT [StudentID], [StudentName], [Student_ContNo] , (CASE WHEN ([Student_ContNo]) LIKE '%1111%' THEN '111X' WHEN ([Student_ContNo]) LIKE '%999%' THEN '999X' WHEN ([Student_ContNo]) LIKE '%88888%' THEN '888X' WHEN ([Student_ContNo]) LIKE '%123456789%' THEN '123X' ELSE 'OK' END) [CF#2]
from [dbo].[#StudentMaster]

Ayyappan.cn,
Dept of ICT.

Mark post(s) as "Answer" that helped you

Ackerly Xu

Contributor

3500 Points

1300 Posts

Re: How to Find-out duplicate contact numbers (in a field)

Feb 13, 2019 02:13 AM|Ackerly Xu|LINK

Hi ayyappan.CNN,

If you want to choose duplicate contact number in you table , you could try the code below.

select a.* , 
case when b.number>1 then 'wrong' else 'right' end result --show the result according to count of phone number
from #studentMaster as a

join   
(
select count(*) as number,Student_ContNo  --select count of number and phone number
   -- if duplicate number exists count will bigger than 1
from #studentMaster group by Student_ContNo  -- group the table by phone number
)
 as b

on  a.student_contno = b.student_contno  -- join grouped table and ogiginal table according to phone number

My original data.

101	Ramki	9141234561
102	Krishna	9999999999
103	Ganesha	8888888888
104	Mani	1111111111
105	Rani	1234567890
106	Nancy	1234567890

The result.

104	Mani	1111111111	right
105	Rani	1234567890	wrong
106	Nancy	1234567890	wrong
103	Ganesha	8888888888	right
101	Ramki	9141234561	right
102	Krishna	9999999999	right

But what do you mean by you query

SELECT [StudentID], [StudentName], [Student_ContNo] , (CASE WHEN ([Student_ContNo]) LIKE '%1111%' THEN '111X' WHEN ([Student_ContNo]) LIKE '%999%' THEN '999X' WHEN ([Student_ContNo]) LIKE '%88888%' THEN '888X' WHEN ([Student_ContNo]) LIKE '%123456789%' THEN '123X' ELSE 'OK' END) [CF#2]
from [dbo].[#StudentMaster]

Best regards,