Casting SMALLINT to CHAR

Database
N/A

Casting SMALLINT to CHAR

I was wondering if anyone else has encountered an issue casting SMALLINTs to CHAR. If I use CAST (field1 as CHAR(5)) (field1 being a SMALLINT datatype), the data displays properly. However, if I use field1(CHAR 5) there are leading blanks, truncating the last digit.
2 REPLIES
Enthusiast

Re: Casting SMALLINT to CHAR

I can only confirm that I experience the same....

================================================================

select 1(smallint);

select 1(smallint);

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

1
------
1

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select 1(smallint)(char(5));

select 1(smallint)(char(5));

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

1
-----

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select -1(smallint)(char(5));

select -1(smallint)(char(5));

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

-1
-----
-

BTEQ -- Enter your DBC/SQL request or BTEQ command:
Enthusiast

Re: Casting SMALLINT to CHAR

Oh I got it. The default character witdth for smallint is 6. That's 5 characters for the numeric portion, then 1 character for the sign. The data values are also right-aligned by default.

==============================

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select 32000(smallint);

select 32000(smallint);

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

32000
------
32000

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select -32000(smallint);

select -32000(smallint);

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

-32000
------
-32000

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select 32000(smallint)(char(5));

select 32000(smallint)(char(5));

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

32000
-----
3200

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select -32000(smallint)(char(5));

select -32000(smallint)(char(5));

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

-32000
------
-3200

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select -32000(smallint)(char(6));

select -32000(smallint)(char(6));

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

-32000
------
-32000

BTEQ -- Enter your DBC/SQL request or BTEQ command: