SQL Query - Group By with Like

Good Day Experts!

I have a situation that I am not sure how to approach.

My query:

Select count([Pro Number]) as myCount From Customer
Group By [Pro Number]
Having count([Pro Number]) > 1

So, I get [Pro Number] values that are duplicates.

I would like to get the following to be considered duplicates:
123456789
CAR123456789

Is this possible?

Thanks,
jimbo99999
Jimbo99999Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Select
    CASE WHEN LEFT([Pro Number], 1) <> 'C' THEN 'CAR' ELSE '' END + [Pro Number] AS [Pro Number],
    Count([Pro Number]) as myCount
From dbo.Customer
Group By CASE WHEN LEFT([Pro Number], 1) <> 'C' THEN 'CAR' ELSE '' END + [Pro Number]
Having count([Pro Number]) > 1


for example:

Select
    CASE WHEN LEFT([Pro Number], 1) <> 'C' THEN 'CAR' ELSE '' END + [Pro Number] AS [Pro Number],
    Count([Pro Number]) as myCount
From (
    select '123456789' as [pro number] union all
    select 'CAR123456789'
) AS test_data
Group By CASE WHEN LEFT([Pro Number], 1) <> 'C' THEN 'CAR' ELSE '' END + [Pro Number]
Having count([Pro Number]) > 1
0
 
Lee SavidgeCommented:
Are the values always prefixed with CAR if they have anything? Some examples would be helpful. If there is a consistent difference then it is as easy as replacing the CAR with an empty string.
0
 
Anthony PerkinsCommented:
The challenge is not skipping the character data, that is trivial to code (although probably a dog to run), the problem is actually reporting the original [Pro Number].  However from looking at your query you do not include it so you may not need it.  All you need is a function (could even be a CLR) to lose the character data, so that your query looks like this:
SELECT  COUNT(*) AS myCount
FROM    Customer
GROUP BY dbo.udf_GetNumericOnly([Pro Number])
HAVING  COUNT(*) > 1

Open in new window

0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Jimbo99999Author Commented:
Thanks for responding.  Yes, the prefix will always be the same like the above example.  I am trying to catch duplicates with this keying error.  I just can't seem to figure out the SQL syntax to add to my query.
0
 
Ross TurnerConnect With a Mentor Management Information Support AnalystCommented:
I don't know if i missing a trick but couldn't you do this

Create a rank based off ProNumbers without the preceding CAR and Rank them.
If it over 1 it's a duplicate.

http://sqlfiddle.com/#!3/24d1f/22

Select * From 
(
Select 
* ,
Rank() over (partition by replace(ProNumber,'CAR','') order by ProNumber) as Ranked
From 
Customer
) As TEST 
Where Ranked > 1

Open in new window

0
 
Anthony PerkinsConnect With a Mentor Commented:
Yes, the prefix will always be the same like the above example.
Then it should be as simple as:
SELECT  COUNT(*) AS myCount
FROM    Customer
GROUP BY SUBSTRING([Pro Number], 4, 50)  -- Change 50 as appropriate
HAVING  COUNT(*) > 1

Open in new window

0
 
awking00Commented:
Can you provide some sample data and your expected results?
0
 
Jimbo99999Author Commented:
Ok, back online.

I have a little more background for the request I was given.  This stems from duplicate keyed values into the system.  The current report I have written finds "exact matches" on the value.  It has come to light, that a particular situation occured where an alpha value is being typed prior to the numeric value.

So, Keyer1 enters order with invoice 123456.  Then, unknowinlgy Keyer2 enters same order for invoice 123456 but puts CARS in front of it due to a misunderstanding in the training process.

I am tasked with finding all of the values in this scenario.  I have to find the following:

123456
CARS123456

987654
CARS987654

But not all CARSxxxxxx will have a corresponding xxxxxx value.

"Thinking through this I need to:"
1)Find all values with CARS in front
2)Then take the numeric value after the CARS and see if that is a value found in the table
3)If 1) and 2) satisfied, then display both on duplicate report
0
 
SharathConnect With a Mentor Data EngineerCommented:
see this.
SELECT * FROM (
SELECT  *,COUNT(*) OVER (PARTITION BY SUBSTRING([Pro Number], 4, 50)) Cnt
FROM    Customer) t1
WHERE cnt > 1

Open in new window

0
 
Jimbo99999Author Commented:
The "little" project has been cancelled.  I appreciate all the responses and have awarded points for your efforts.  In my spare time I will still try to get it working for future reference.

Thanks again,
jimbo99999
0
All Courses

From novice to tech pro — start learning today.