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

• • Reply
  ayyappan.CNN

  Member

  318 Points

  442 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

  318 Points

  442 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
• • Reply
  Ackerly Xu

  Contributor

  3420 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,

  Ackerly Xu

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
• • Reply
  ayyappan.CNN

  Member

  318 Points

  442 Posts

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

  Feb 14, 2019 11:17 AM|ayyappan.CNN|LINK

  Hi, 

  Pls try this... 

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

  Ayyappan.cn,
  Dept of ICT.
  
  Mark post(s) as "Answer" that helped you
• • Reply
  limno

  All-Star

  122895 Points

  9913 Posts

  Moderator

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

  Feb 14, 2019 07:28 PM|limno|LINK

  Do you have a definition of duplicate contact numbers?  The way you defined is not duplicate but some numbers you don't like.

  Jingyang Li
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  ayyappan.CNN

  Member

  318 Points

  442 Posts

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

  Feb 15, 2019 05:11 PM|ayyappan.CNN|LINK

  Hi, 

  All repeated numbers (9999999,111111) and 1234567890 are wrong and rest of the contact numbers are correct.

  Thanks,

  Ayyappan.cn,
  Dept of ICT.
  
  Mark post(s) as "Answer" that helped you
• • Reply
  yogyogi

  Participant

  1000 Points

  687 Posts

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

  Feb 16, 2019 12:02 PM|yogyogi|LINK

  Duplicate records can be removed from a table by a number of ways. Mostly people use Group By clause for doing this. Have a look on this article which explains all these methods - 5 Quick Ways To Delete Duplicate Records From Database Which Every Developer Must Know

  Regards

  Helping you always. Don't forget to click "Mark as Answer" on the post that helped you.
  ♠ ASP.NET Core Tutorials → Start from the Beginning and become an Expert in 30 days time. ♠