The "Right" command & zero filling the data

chubnut · August 17th, 2004, 08:13 AM

Hi everyone, I have a problem and I need some help.

We are using SQL to format data in tables in preparation for uploading to another system. The other system requires that numeric data be zero filled for uploading with no decimal places, so 192.30 will become 0000019230 .

In access I used to be able to "pad" the data using something like the following:-

UPDATE DISTINCTROW [rec26 1] SET [rec26 1].[dd2683,1] = Right("000000000" & [rec26 1].[dd2683,1],9), [rec26 1].[dd2686,1] = Right("000000000" & [rec26 1].[dd2686,1],9);

Obviously in SQL, can't use "&" for something like this so I thought "+" would be the solution. It just doesn't seem to do anything.

Can anyone shed some light on this issue

Many thanks

happygv · August 17th, 2004, 05:13 PM

What is the datatype of the field that contains values with decimals? 192.30

_________________________
- Vijay G
Strive for Perfection

chubnut · August 18th, 2004, 09:43 AM

The field I am trying to update is a char field. Don't worry about the source field as that is a decimal from another table altogether, and therefore not part of the problem.

bmains · August 18th, 2004, 11:21 AM

You can do that in SQL also, I've done something like:

right('0000000000' + replace(convert(char, [rec26 1]), '.', ''), 10)

Just tested this out in my DB and it works in SQL Server.

Brian

happygv · August 18th, 2004, 09:29 PM

You can try this too, as an alternative.

Code:

Replace(replicate('0',10-len([rec26 1])) + [rec26 1],'.','')

OR

Code:

replicate('0',9-len(replace([rec26 1],'.',''))) + replace([rec26 1],'.','')

Cheers!

_________________________
- Vijay G
Strive for Perfection