SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Odd Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cosmarchy
Starting Member

14 Posts

Posted - 03/03/2014 :  04:25:41  Show Profile  Reply with Quote
Hi,

I am looking for a query which can determine duplicates of part of a field. For example I have data in the following format:

XAP0000100
XAP0000100-1
XAP0000100-2
XAP0000101
XAP0000101-1
XAP0000102
XAP0000103
XAP0000103-1
XAP0000103-2
XAP0000103-3

What I want to do is get the duplicates but ignore the suffix of -n.
So, for example the first duplicate will be

XAP0000100
XAP0000100-1
XAP0000100-2

because the XAP0000100 part is duplicated.

The second duplicate would be
XAP0000101
XAP0000101-1

again, the duplicate part will be XAP0000101.

It would ignore XAP0000102 as there are no other duplicates.

The next duplicate would be

XAP0000103
XAP0000103-1
XAP0000103-2
XAP0000103-3

and so on.

Hopefully this makes sense?

If anyone could help with this it would be much appreciated.

Many thanks

stepson
Constraint Violating Yak Guru

Romania
403 Posts

Posted - 03/03/2014 :  05:05:35  Show Profile  Reply with Quote
hi,

What is the final output?


;with aCTE
AS(
	select 'XAP0000100' as Duplicates union all
	select 'XAP0000100-1' union all
	select 'XAP0000100-2' union all
	select 'XAP0000101' union all
	select 'XAP0000101-1' union all
	select 'XAP0000102' union all
	select 'XAP0000103' union all
	select 'XAP0000103-1' union all
	select 'XAP0000103-2' union all
	select 'XAP0000103-3')

,newCTE
AS (
	select duplicates
			,case when charIndex('-',duplicates) >0 then  left(duplicates,charIndex('-',duplicates)-1)
				else duplicates
				end as newValue	
	from aCTE)
	



select * 														
from newCTE





duplicates	newValue
XAP0000100	XAP0000100
XAP0000100-1	XAP0000100
XAP0000100-2	XAP0000100
XAP0000101	XAP0000101
XAP0000101-1	XAP0000101
XAP0000102	XAP0000102
XAP0000103	XAP0000103
XAP0000103-1	XAP0000103
XAP0000103-2	XAP0000103
XAP0000103-3	XAP0000103



S


sabinWeb MCP
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/03/2014 :  06:02:59  Show Profile  Reply with Quote

SELECT *
FROM
(
SELECT *,COUNT(1) OVER (PARTITION BY LEFT(ColumnName,CHARINDEX('-',ColumnName + '-')-1)) AS Cnt
FROM Table
)t
WHERE Cnt > 1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

cosmarchy
Starting Member

14 Posts

Posted - 03/03/2014 :  07:05:37  Show Profile  Reply with Quote
@stepson

The output is exactly as you describe however I have a lot more data than this so would not want to be selecting all of the data!!

@visakh16

This looks rather neat :)
I also would have an additional application for this in an Access DB - I don't suppose you would know how to make this suitable for that also would you?

Thanks for your assistance guys.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/03/2014 :  10:47:04  Show Profile  Reply with Quote
quote:
Originally posted by cosmarchy

@stepson

The output is exactly as you describe however I have a lot more data than this so would not want to be selecting all of the data!!

@visakh16

This looks rather neat :)
I also would have an additional application for this in an Access DB - I don't suppose you would know how to make this suitable for that also would you?

Thanks for your assistance guys.



Unfortunately no

I've not worked on Access

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/03/2014 :  10:47:10  Show Profile  Reply with Quote
quote:
Originally posted by cosmarchy

@stepson

The output is exactly as you describe however I have a lot more data than this so would not want to be selecting all of the data!!

@visakh16

This looks rather neat :)
I also would have an additional application for this in an Access DB - I don't suppose you would know how to make this suitable for that also would you?

Thanks for your assistance guys.



Unfortunately no

I've not worked on Access

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
403 Posts

Posted - 03/04/2014 :  01:56:39  Show Profile  Reply with Quote
in access:



SELECT 
 
IIF(  InStr(1,field1,"-")>0,  Mid(Field1,1, InStr(1,field1,"-") -1 ) ,Field1)  AS Field

, *
FROM Table1;




sabinWeb MCP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000