select duplicate record where substring of one row is equal to substring of another row

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

select duplicate record where substring of one row is equal to substring of another row
using partition by query ex. SN2065= SN2065#1 or SN2065= SN2065#2

Posted 20-Aug-14 21:03pm

aparnaChandras

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Salman622 · Answer 1 · 2014-08-20T21:20:00

Solution 1

SQL

with cte
as
(
select ID from TableName
Group By ID
Having Count(SUBSTRING(ColumnName,StartIndex,EndIndex))>1
)
Select * from TableName s,cte c where s.ID=c.ID and s.ID='YourID'

Hope it will help you

Posted 20-Aug-14 21:20pm

Salman622

Comments

aparnaChandras 21-Aug-14 3:24am

I have use following query but it will not work , how to modify the same query ?
;with CTE_data
([Seq],
[ENGINENO],
[HULLNO],
[EngineType],
[ENO],
[HNO],
[Etype],
[Licensee],
[Engine_OWNER],
[YARD],
[DLDATE],
cn,
RowNumber
)
AS
(
SELECT a.[Seq],
a.[ENGINENO],
a.[HULLNO],
a.[EngineType],
a.[ENO],
a.[HNO],
a.[Etype],
a.[Licensee],
a.[Engine_OWNER],
a.[YARD],
a.[DLDATE],
COUNT(*)over (PARTITION by [HULLNO]+'%')

,
RowNumber = ROW_NUMBER() over (PARTITION by [HULLNO]+'%' order by a.[Seq])

FROM APDE_6Aug.[dbo].[EngineMaster_Duplicates] a where
(
[HULLNO] like SUBSTRING(a.[HULLNO],1,LEN([HULLNO]))
)
and LEN([HULLNO])>1
)
Select * from CTE_data where Cn>2

Salman622 21-Aug-14 3:30am

what value is there in hullno
is it SN2065 or (SN2065#1 ....)
and what is the primary key of table EngineMaster_Duplicates

aparnaChandras 21-Aug-14 3:35am

both values are there in different row in same column

aparnaChandras 21-Aug-14 3:36am

Seq ENGINENO HULLNO
55525 HYUNAA5344 SN2066
55526 HYUNAA5345 SN2066
105793 AA5344 SN2066
105795 AA5344 SN2066
113532 NULL SN2066 # 1
113533 NULL SN2066 # 2

I have to show that these are duplicates

Salman622 21-Aug-14 3:45am

with Cte
as
(
select HULLNO from EngineMaster_Duplicates
Group By HULLNO
Having Count(SUBSTRING(HULLNO,1,6))>1
)
Select * from EngineMaster_Duplicates s,cte c where s.HULLNO =c.HULLNO and s.VoucherNo Like 'SN2066%'

try this

Salman622 21-Aug-14 3:47am

Instead of * you can use Column Names of EngineMaster_Duplicates

Like
s.Seq,s.ENGINENO,s.HULLNO

Salman622 21-Aug-14 4:10am

with Cte
as
(
select HULLNO from EngineMaster_Duplicates where HULLNO Like 'SN2066%'
Group By HULLNO
Having (Select Count(HULLNO) as cn from EngineMaster_Duplicates where HULLNO Like 'SN2066%')>1
)
Select * from EngineMaster_Duplicates s,cte c where s.HULLNO =c.HULLNO

this will work definately try and let me know

aparnaChandras 21-Aug-14 4:20am

i want generic solution this has specific value

Salman622 21-Aug-14 4:31am

You want each and Every record which is duplicate

aparnaChandras 21-Aug-14 4:34am

yes

Salman622 21-Aug-14 4:34am

Its Simple so you can use like this

with Cte
as
(
select HULLNO from EngineMaster_Duplicates where HULLNO Like HULLNO+'%'
Group By HULLNO
Having (Select Count(HULLNO) as cn from EngineMaster_Duplicates where HULLNO Like HULLNO+'%')>1
)
Select * from EngineMaster_Duplicates s,cte c where s.HULLNO =c.HULLNO order by s.HULLNO

aparnaChandras 21-Aug-14 4:58am

No this is not working