Odd Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Odd Query

Author

Topic

cosmarchy
Starting Member

14 Posts

Posted - 2014-03-03 : 04:25:41

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
Aged Yak Warrior

545 Posts

Posted - 2014-03-03 : 05:05:35

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-03 : 06:02:59

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

cosmarchy
Starting Member

14 Posts

Posted - 2014-03-03 : 07:05:37

@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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-03 : 10:47:04

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-03 : 10:47:10

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

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-04 : 01:56:39

in access:



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

, *
FROM Table1;

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources