Converting int value to a char that is too small

Question

Try the following on MSSQL 2005:

select convert(char(2), 123)

The key here is that char(2) is too small to accept the value '123'. I would expect to see a truncation error here, but instead the value "*" is returned.

Update: A few of the answers showed how to cast in a way that will cause an error. That's not really what I need. We have lots of code that uses a specific field that used to be declared char(2) but has since been changed to int. My goal here is to make sure that code that hasn't been converted will fall over if it encounters data that it can't handle. So we can go fix it.

Interestingly, dsolimano notes that changing the above type to nchar causes the expected error, and Justin Niessner notes that both of these are by design. Strange inconsistency given that nchar is for Unicode support, hey?

From the answers I have here, it appears that I sadly can't make the server throw the error for existing code.

Answer 1

Just cast it to a variable length string before you cast it to a string that is too small.

Select Cast( Cast( 123 as varchar(10) ) As char(2) )

Answer 2

SQL Server will truncate strings without a problem. So you could convert the value to a varchar first, and then cast it to char(2):

select convert(char(2), cast(123 as varchar(128))

Answer 3

Scroll down the following MSDN Page:

CAST and CONVERT (Transact-SQL)

Until you get to the heading Truncating and Rounding Results

You'll see that the behavior you're seeing is the defined behavior.

If you want the truncation to occur so that you get only two digits of the number, then you can try:

select cast(convert(varchar(10), 123) as char(2))

Answer 4

Depends what you mean by "prevent" this from happening? What would you like to happen instead? Nothing?

DECLARE @number INT
SET @number = 123

IF(@number < 100) SELECT CONVERT(char(2), @number)

Answer 5

Microsoft's convert/cast page seems to indicate that you can cast to nchar(2) if you want to get an error instead of * or truncation, and indeed that is what I get:

SELECT CAST(123 AS NCHAR(2))

Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type nvarchar.