All Forums Analytics
Benjamin Marcus 48 posts Joined 03/07
11 Jul 2007
Concatenate Number, How to remove dot (.) in the result?

Dear who know please help,Currently I am concatenate 3 number as below,60122011506 60169496573 6285224707971The 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, 6285224707971Thanks :-)

Regards Benjamin Marcus
joedsilva 505 posts Joined 07/05
11 Jul 2007

Try playing with format and trim ...... like this ..SELECT TRIM(60122011506 (FORMAT 'Z(17)9') ) || ','

Benjamin Marcus 48 posts Joined 03/07
11 Jul 2007

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^_^

Regards
Benjamin Marcus

joedsilva 505 posts Joined 07/05
11 Jul 2007

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 0so if you do ZZ formating for 7, you get 799 formating will give 07and Z9 formating will also give 7but ZZ formating of 0 gives two space characterswhere as Z9 formating of 0 will give 0Formating can be a bit tricky and is a big enough subject, you can get more details from data types & literals manual.

davidpracy 22 posts Joined 09/05
11 Jul 2007

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));123345select cast (123 as char(4)) || cast(345 as char(4));123 345 dp

Benjamin Marcus 48 posts Joined 03/07
11 Jul 2007

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

Regards
Benjamin Marcus

davidpracy 22 posts Joined 09/05
12 Jul 2007

The examples that I provided were just examplesspecify the size of the Char field so as to meet you requirement/field sizeIt is easier to help questions when they have DDL,queries etc supplied.

You must sign in to leave a comment.