Concatenate Number, How to remove dot (.) in the result?

Analytics
Enthusiast

Concatenate Number, How to remove dot (.) in the result?

Dear who know please help,

Currently I am concatenate 3 number as below,
60122011506 60169496573 6285224707971

The Result:
60122011506., 60169496573., 6285224707971.

Something funny,
I notice that there are dot(.) after the number in the result.

How to remove the dot(.)?
so the result will show:
60122011506, 60169496573, 6285224707971

Thanks :-)
6 REPLIES
Enthusiast

Re: Concatenate Number, How to remove dot (.) in the result?



Try playing with format and trim ...... like this ..

SELECT TRIM(60122011506 (FORMAT 'Z(17)9') ) || ','
Enthusiast

Re: Concatenate Number, How to remove dot (.) in the result?

Thanks a lot.
It work excellent. You are genius!

If you don't mind, please may you tell me more about this format:
FORMAT 'Z(17)9'
I didn't saw it before,what is Z and 9 mean?

Thanks^_^
Enthusiast

Re: Concatenate Number, How to remove dot (.) in the result?

Z, when used with integer types implies a place holder for a digit, which if there are no digits, could be supplemented with a space. 9, on the other hand will supplement a missing digit with 0

so if you do ZZ formating for 7, you get 7
99 formating will give 07
and Z9 formating will also give 7

but ZZ formating of 0 gives two space characters
where as Z9 formating of 0 will give 0

Formating can be a bit tricky and is a big enough subject, you can get more details from data types & literals manual.
Enthusiast

Re: Concatenate Number, How to remove dot (.) in the result?

Try casting the fields as Chars then concatenate them.

Essentially this is what you are doing (if the field is defined as numeric)

select cast (123 as numeric) || cast(345 as numeric);
123. 345.

Either of these may give you what you want:

select cast (123 as char(3)) || cast(345 as char(3));
123345

select cast (123 as char(4)) || cast(345 as char(4));
123 345

dp
Enthusiast

Re: Concatenate Number, How to remove dot (.) in the result?

Dear David,
First of all, thanks for your reply and suggestion.

1) If you use cast (123 as numeric), it unable to run on big number such as 60283556289973, it will return "Numeric number overflow".
2) If you use cast (123 as char(4)), for big number like 60283556289973, it will return something like 6.000318283E number.

Both methods I did try and the result not goes with my requirement.

Thanks :-)
Enthusiast

Re: Concatenate Number, How to remove dot (.) in the result?

The examples that I provided were just examples

specify the size of the Char field so as to meet you requirement/field size

It is easier to help questions when they have DDL,queries etc supplied.