How to identify records, that after removing leading zeros results in deuplicates

Question

Post reply

How to identify records, that after removing leading zeros results in deuplicates

GF

Hall of Fame

Points: 3774
More actions
June 18, 2014 at 7:11 pm

#99916

Hello,
I have a table called customer_po_numbers
Table design
Customer_id int not null,
Po_number varchar(50) not null
Currently there is data in the table that should not be there due to poor design. We are working to correct but first must clean up the data.
Example:
Customer_id, po_number
123456 , 0000776887
123456 , 776887
123456 , 00776887
456376 , 0653452
456376 , 653452
What I need is a way to identify the customer_id and po_numbers that when the leading zeros are removed would produce a duplicate customer_id and po_number
There is a unique constraint on the table that does not allow duplicate customer_id and po_number
Currently there are over 800,000 rows of data in this table.
So I need to identify the affected data first before any action is taken.
Any help is appreciated.
Thanks
Gary

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

should work:

SELECT customer_id, CONVERT( INT, po_number), COUNT(*)

FROM customer_po_numbers

GROUP BY customer_id, CONVERT( INT, po_number)

HAVING COUNT(*) > 1

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

GF Hall of Fame Points: 3774 More actions · Answer 2

Craig,

thank you for your response.

However it returned no results.

Gary

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

GF (6/18/2014)
Craig,
thank you for your response.
However it returned no results.
Gary

Then either you don't have any duplicates or you didn't do a very good explaining the issue. This works perfectly with your sample data provided. In the future if you could provide your sample data in a consumable format like this you will get a lot more responses.

create table #customer_po_numbers

(

customer_id int,

Po_number varchar(50)

)

insert #customer_po_numbers

select 123456 , '0000776887' union all

select 123456 , '776887' union all

select 123456 , '00776887' union all

select 456376 , '0653452' union all

select 456376 , '653452'

SELECT customer_id, CONVERT( INT, po_number), COUNT(*)

FROM #customer_po_numbers

GROUP BY customer_id, CONVERT( INT, po_number)

HAVING COUNT(*) > 1

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

GF Hall of Fame Points: 3774 More actions · Answer 4

Thank you Craig and Sean,

The problem was user error.

There were in fact no duplicate but at the time I was certain there were.

I was at the end of an 18 hour shift and pretty much brain dead.

My apologies to you both.

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

GF (6/19/2014)
Thank you Craig and Sean,
The problem was user error.
There were in fact no duplicate but at the time I was certain there were.
I was at the end of an 18 hour shift and pretty much brain dead.
My apologies to you both.

I can't speak for Craig but I have certainly been there. No worries. Glad it worked for you.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

Sean Lange (6/19/2014)
GF (6/19/2014)
Thank you Craig and Sean,
The problem was user error.
There were in fact no duplicate but at the time I was certain there were.
I was at the end of an 18 hour shift and pretty much brain dead.
My apologies to you both.
I can't speak for Craig but I have certainly been there. No worries. Glad it worked for you.

I'm with my mouth all day and I'm convinced now and then someone else should definitely speak for me. 😉 No worries GF, you're fine, been there myself.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Sean Lange SSC Guru Points: 286573 More actions · Answer 7

Evil Kraig F (6/20/2014)
I'm with my mouth all day and I'm convinced now and then someone else should definitely speak for me. 😉

I know what you mean there. I can safely say that you do NOT want your surrogate mouthpiece to be me. I might get you in more trouble than you do on your own. :w00t:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/