Thursday, January 1, 2015

CONVERT returns '*' - [Happy New Year]

Let's start with a simple thing in 2015. Why we get '*' when converting one type to another? If you have already experienced it, then you know the reason, if not here is the reason;

DECLARE @Integer int = 123;

SELECT 'My Integer is ' + CONVERT(char(2), @Integer);

Analyze the code above, as you see, CONVERT function returns '*' instead of '12'. This could happen with conversion because when converting from one type to another, data may be truncated, might be appeared as cut-off, or an error could be thrown because the new type is not fit enough to display the result. The conversion in the above code tries to convert 123 into char(2) which is too small to hold the value, hence displays '*'.

However, this behavior depends on the types involved. If you are converting varchar(3) to char(2), do not expect '*' but a cut-off value.

And all SQL lovers and my followers;

No comments: