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 (
insert into XYZ (
Sel 301 AS EVT_ID;
sel * from ud505.XYZ
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
The length of the field is 4 instead of 3.
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;