All Forums Database
Stiphu 5 posts Joined 07/13
06 Jan 2014
Converting Number to Character

I have a numeric value which I want to divide by 1000:

SELECT DISTINCT
    Speed_Val_Down AS svd,
    CAST(svd AS FLOAT) / 1000 AS div,
    CAST(res AS VARCHAR(50)) AS res
FROM
    SPH
ORDER BY
    1;

The numeric value svd is Decimal(18,0), the Result can be something between 0.011 an 1000. Now my Problem is that the string result is in the scientific format (2.400000000000E 000), but I would like to have the normal format as I have it as a floating number. An example:
The value for swd is 2400, so the result div is 2.4 and the character result is "2.40000000000000E 000", but I would like to have the res to be the same as the div. Any suggestion?
 

M.Saeed Khurram 544 posts Joined 09/12
07 Jan 2014

Hi Stiphu,
 
Try this:

SELECT 
     CAST(CAST(2400 AS DECIMAL(18,1))  / 1000 AS VARCHAR(6))AS div

 

Khurram

Raja_KT 1246 posts Joined 07/09
07 Jan 2014

Hi Stiphu,
You can cast as you require:

/*created table*/

CREATE MULTISET TABLE db1.raja_test ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      id INTEGER,

      name VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,

      query VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)

NO PRIMARY INDEX ;

 

/*query result normal */

select id,name,query from db1.raja_test WHERE ID=4

id name query

4 6782 2.400000000000E000

 

query after casting to float.

select id,name,cast(query as float) from db1.raja_test WHERE ID=4

id name query

4 6782 2.40

 

Is this what you want? You can cast your varchar .

 

Cheers,

 

 

 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Stiphu 5 posts Joined 07/13
07 Jan 2014

Thank you for the responses.
It's not exactly what I wanted.
 
@Saeed: I tried this as well. The result is fine for a number which has a value behind the comma, but not for all of the following examples:
number   result   wished result (char)
0            .0         0
11          .0         0.011
1200       1.2       1.2
18750     18.8     18.75
 
@Raja: I want to cast decimal numbers (as shown in the example for @Saeed) into char values with the right formatation, not char values into foating values.
 
Any help is very welcomed!
Cheers, Stephan

M.Saeed Khurram 544 posts Joined 09/12
07 Jan 2014

Stephen,
Basically in order to avoid this round off you need to devide by the same percision as the value.
for example in case of 18750 we need to case it to decimal to get result as decimal and we need to devide it by 1000.00 to obtain correct percision in result.
for value 11, you want to get result .011, can you please tell how many digits in percision you need?
else you can use query like this:

SELECT 
CAST(COL_NAME  AS DECIMAL(18,2))  / 1000.00

 

Khurram

Stiphu 5 posts Joined 07/13
07 Jan 2014

Khurram,
I don't need a fix amount of digits. for the number 0.011 for instance, i need 3 digits after the comma. For the number 12.100, I need one digit (12.1), for the number 14.000 I need no digits after the comma (14). I just want the result to be displayed as I described before under "wished result".
Regards

M.Saeed Khurram 544 posts Joined 09/12
07 Jan 2014

So you want to suppress any trailing 0z,
This will work for you:

   SELECT 
   CAST(11  AS DECIMAL(18,6))  / 1000.00  (FORMAT 'GZ(I)DZZZZZ')  

 

Khurram

dnoeth 4628 posts Joined 11/04
07 Jan 2014

What's your TD release?
In TD14 there's a new FORMAT used for NUMBERs, the default FORMAT 'FN9' should work:

CAST(svd AS NUMBER) / 1000 as div,
CAST(div AS VARCHAR(50)) AS res

Before TD14 you can do something like:

CAST(svd AS FLOAT) / 1000 AS div,
TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM (TRIM(CAST(div AS DECIMAL(38,19))))))

 

Dieter

You must sign in to leave a comment.