CODEWITHSUNDEEP
sql-server-2008
Sharad
Sharad

Reputation: 813

Why * is Inserted in SQL

This happened when I was just testing. I've created a table as

    Create Table Test_Table
(
    Field_char char(1)
);

When I want to insert value with code

Insert Into Test_Table(Field_char)
Select 13;

It inserts '*' in the column. For single digits it inserts them as it is. If the length is modified from 1 to 2, similar thing happen for 3 digits input such as 100 etc.

Why is this?

Upvotes: 2

Views: 111

Answers (3)

DonMushroom
DonMushroom

Reputation: 442

In your create statement you set the length of Field_char to 1 (char(1)). This means that your entries must have a length smaller or equal to 1. valid entries are 1,2 etc. Invalid entries are 12, 13 as they are longer than 1 char -> * is a placeholder to indicate invalid values.

EDIT: (Thanks To Vladimir)

To be more precise take a look here.

Truncating and Rounding Results

[...] Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.

There we have the following entry:

From data type int to data type char result *

where * = Result length too short to display

Upvotes: 8

Dhaval
Dhaval

Reputation: 2379

enter image description here It simply Convert Int to Char

for Example 

select CONVERT(char(1),13)

it will give *

Sql Implicitly convert int to char which is you column type..

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

When you are writing 

Insert Into Test_Table(Field_char)
Select 13;

The it is converting int to char. So your 13 is converted into *. If you want you can check by writing

select CONVERT(char(1),13)

If you want to see the result as 13 then you need to put that in single inverted comma like this:

Insert Into Test_Table(Field_char)
Select '13';

And also you need to increase the size of column as char(1) can hold only one character.

SQL FIDDLE DEMO

Upvotes: 1

Related Questions