Char variable returns asterisk when inserted value exceeds char length by jiri_hubacek

Closed

as By Design Help for as By Design

Type:	Suggestion
ID:	772540
Opened:	11/28/2012 9:17:54 AM
Access Restriction:	Public

Workaround(s)

view

DECLARE @a char(1)
-- @a is null
SELECT @a = 123
select @a

Returns *
'Asterisk'

Changing the source data type to nchar throws an error:
DECLARE @a nchar(1)
-- @a is null
SELECT @a = 123
select @a

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

DECLARE @a char(1)
-- @a is null
SELECT @a = '123'
select @a

Truncates the result.

Database Engine version: 10.0.5500, 10.50.4000

Details (expand)

English

SQL Engine

Following query should results in Arithmetic overflow error:
DECLARE @a char(1)
-- @a is null
SELECT ISNULL(@a,123)

Improved Reliability

Unified behaviour of char and nchar data types.

0 attachments

Posted by Microsoft on 11/29/2012 at 2:22 PM

Hello,
Thanks for your feedback. The behavior you are seeing is by design.See table "Truncating & Rounding Results" in below link.

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Changing it will result in incompatibility with existing applications.

--
Umachandar, SQL Programmability Team

Microsoft