Decimal datatype adding extra space

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
New Member

Decimal datatype adding extra space

Hello,

I create a table with a field for decimal data type. When I cast it to VARCHAR and check the length it seems as if an extra space has been added to the field. Anyone experienced this before, and how to solve for it?

 

create multiset table XYZ (
EVT_ID DECIMAL(5)
) ;

insert into XYZ (
EVT_ID)
Sel 301 AS EVT_ID;

sel * from ud505.XYZ

sel
LENGTH (trim(CAST(EVT_ID AS VARCHAR(50)))) as evt_id_len
,SUBSTRING(trim(CAST(EVT_ID AS VARCHAR(50))),0,3) AS evt_id_2
from
XYZ;

 

The length of the field is 4 instead of 3.

1 REPLY
Junior Contributor

Re: Decimal datatype adding extra space

It's not adding a space, this is based on the default format of a decimal which includes a decimal point.

SELECT
   Cast(EVT_ID AS VARCHAR(50))
  ,Char_Length(Trim(Trailing From EVT_ID)) AS evt_id_len
  ,Substring(Cast(EVT_ID AS VARCHAR(50)) FROM 1 FOR 3) AS evt_id_2
FROM XYZ;