Adding 0 in front and behind number

Other SQL Server Topics
1

Hello guys, can i get some help? Im beginner in SQL, I have a weight value, that comes in format of xxx,xxxx (kg)

so as a result of my script I have a number like 382,0000 (which is 382 kg to be clear) and I need to get it like this: 038200 (note - most of values is around 300-400, there might be some extra rare 1000-1200 value)
in the script bellow it is the weight value, i need to change after i get it from database. Thank you for help in advance

this is my script:

select
QZBOZIVMJADD.idzbozi,
QZBOZIVMJADD.MNMJHMOTNOST as weight

from QZBOZIVMJADD
left join sdok on sdok.idzbozi = QZBOZIVMJADD.idzbozi
left join hdok on hdok.idhdok = sdok.idhdok
left join zbozi on zbozi.idzbozi = sdok.idzbozi

where
QZBOZIVMJADD.idzbozi=%idzbozi% and QZBOZIVMJADD.IDMJ = 18

2

use:

RIGHT('00' + CAST(QZBOZIVMJADD.MNMJHMOTNOST as VARCHAR(6)), 6) as weight
3

What is the datatype of the original data, please?

4

I think it would be better to convert the weight into a numeric column (either Grams or Decimal Kilograms) and then use the presentation layer to fix-format the amount with leading zeros.

That is better done in the application, but if it needs to be done in SQL then I would use a VIEW to "present" the data.

Converted to String, so that leading zeros can be added, that value is no longer numeric and won't be treated by any consumer, downstream, as numeric ...

I know, with leading zeros, it will parse & sort as if it was numeric, but there always seems to turn out to be a Gotcha with such solutions ...

5

@Zaily,

It could be that this is for a fixed field format file. If it's not, then I absolutely agree with Kristen.

You also still haven't answered my previous question.

6

Hi guys, sorry, I was offline for weekend.
datatype was decimal number, i changed it into INTEGER, so now I get just 382 , but still I dont know how to add one 0 before number and two 0 on its right side. Could You give me some example of SQL sentence that adds them on both sides?
I need this number, to use it in EAN bar code as weight (3302) identifier, Im not sure I can use only 382 number, because in example of EAN i got weight written as 038200

Thank You for your replies

7

The weight part (with identifier) of the GS1-128, can be presented like this:
3102000000+QZBOZIVMJADD.MNMJHMOTNOST*100