CODEWITHSUNDEEP
sqlselectsql-server-2012group-by
Saptarsi Mondal
Saptarsi Mondal

Reputation: 13

SQL Server Select query multiple results

I am using SQL Server 2012 (SP3-CU2) (KB3137746)

I have a table POLICY and trying to select rows matching with policyNumber. This column is of type VARCHAR.

First case:

select * 
from POLICY 
where PolicyNumber = 1234

It returns two rows

PolicyNumber  CustomerId
------------------------
001234        000789
1234          000567

Next, I am trying to find those cases in tables - how many such cases are there?

Second case:

Select Count(1) As Number_Of_policy, PolicyNumber     
From POLICY 
Group By PolicyNumber    
Having Count(1) > 1

It is not returning the intended rows.

Then I modified the query

Third case:

Select Count(1) As Number_Of_policy, CAST(PolicyNumber AS int) policyNum   
From POLICY
Group By CAST(PolicyNumber AS int)    
Having Count(1) > 1

It is returning rows.

My question is why this behaviour while writing normal select query? Is it by default type case and returned rows in the first case?

Upvotes: 1

Views: 75

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

The text 001234 is not the same thing as the text 1234. But if you cast both to integers, then they become the same number 1234.

The moral of the story here is that if you intend the policy numbers to behave just as that, namely numbers, then store them in a numeric column.

If you are wondering why your first query returns two rows:

SELECT * from POLICY WHERE PolicyNumber = 1234;

In this case, SQL Server is doing an implicit conversion on PolicyNumber to integer.

Upvotes: 2

Related Questions