Converting Number to Character

Database
Enthusiast

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?

7 REPLIES
Enthusiast

Re: Converting Number to Character

Hi Stiphu,

Try this:

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

Re: Converting Number to Character

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,

Enthusiast

Re: Converting Number to Character

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

Enthusiast

Re: Converting Number to Character

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
Enthusiast

Re: Converting Number to Character

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

Enthusiast

Re: Converting Number to Character

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
Senior Apprentice

Re: Converting Number to Character

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))))))