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]
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
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.
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]
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 ♠
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
Expected Result is..
Thanks in advance.
Dept of ICT.
Mark post(s) as "Answer" that helped you
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]
Dept of ICT.
Mark post(s) as "Answer" that helped you
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.
My original data.
The result.
But what do you mean by you query
Best regards,
Ackerly Xu
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.
Member
364 Points
536 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...
Dept of ICT.
Mark post(s) as "Answer" that helped you
All-Star
123252 Points
10024 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.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Member
364 Points
536 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,
Dept of ICT.
Mark post(s) as "Answer" that helped you
Participant
1253 Points
943 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
♠ ASP.NET Core Tutorials → Start from the Beginning and become an Expert in 30 days time ♠