TableA
OFIPS int null,
DFIPS int null
Query below returns a record.
SELECT OFIPS from TableA Where OFIPS = 01077
But this query doesn't.
SELECT OFIPS from TableA Where CAST(OFIPS as nvarchar(5)) = '01077'
Not sure what the problem is. Please help.
Because in int column leading zero's will be ignored, 01077 will be stored as 1077 and when you query like
Where OFIPS = 01077
It will be parsed as
Where OFIPS = 1077
There will be a record stored as 1077 so you are getting result.
But when you query like
Where CAST(OFIPS as nvarchar(5)) = '01077'
It checks for the records with 01077 thats why you are not getting result in second query
Interestingly if you remove the CAST in Where clause again it will work. Even though you query with leading zero in string format.
Where OFIPS = '01077'
Because LHS OFIPS has INT datatype which has higher precedense than the RHS Varchar. Implicitly string in the RHS(01077) will be converted to int 1077
Implicit Conversion
When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.
https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql
Meaning, that '01234' = 1234 will result to TRUE, since '01234' will be converted to INT. INT has a higher precdence.
Explicit conversion
Converting INT to VARCHAR - like CONVERT(VARCHAR(5), 1234) - will not add leading zeros therefore '01234' not equals to CONVERT(VARCHAR(5), 1234).
You can forcibly add the leading zeros if you have a fixed length: RIGHT(CONCAT('00000', CONVERT(CHAR(5), 1234)), 5)
Remarks
Consider to keep the INT-INT comparison, force convert the input values to INT instead of converting the column values.
If the above is not possible, consider to store the values in the database as fixed length character values (CHAR(5)) and make sure, that the INSERT and UPDATE statements are transforming the values to their desired format.
Your column is int and the only int that can have a leading 0 is 0.
Other numbers have no leading 0 because they are not strings but numbers.
When you search for Where OFIPS = 01077 01077 is implicitly converted to 1077,
and this number exists in your table.
When you do CAST(OFIPS as nvarchar(5)) no leading 0 can appear so no one number will be casted to '01077'
You can use this because OFIPS of type is int. Int type is not '01077'. I hope that it help you.
SELECT OFIPS from TableA Where CAST(OFIPS as nvarchar(5)) = '1077'
