CODEWITHSUNDEEP
sql-servert-sql
user1
user1

Reputation: 4131

Sql: Casting INT to string

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.

Upvotes: 1

Views: 21125

Answers (4)

Pred
Pred

Reputation: 9042

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.

Upvotes: 1

Tien Nguyen Ngoc
Tien Nguyen Ngoc

Reputation: 1555

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'

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

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

Upvotes: 8

sepupic
sepupic

Reputation: 8687

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'

Upvotes: 0

Related Questions