1. Registered User
Join Date
Feb 2004
Location
Posts
46

I have one table that has a column 'amount' with datatype 'numeric(19,5)'
I'm want to insert the data from this column into another column (in a different database) with this format: '00000000.00' as 'char'
example: If in the first column I have 800.75864 I want to insert it in the other column as '00000800.75'

any suggestions on how to make this conversion?

2. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
This is freaking blowing my mind...

This should work
Code:
```DECLARE @x decimal(15,5), @y char(11)
SELECT @x = 800.75864
SELECT @x, RIGHT(REPLICATE('0',11)+CONVERT(char(11),CONVERT(decimal(15,2),@x)),11)```
But's it's acting like it's doing an implicint conversion to numeric...

Hold on..

If I do...

Code:
```DECLARE @x decimal(15,5), @y char(11)
SELECT @x = 800.75864
SELECT @x, REPLICATE('0',11)+CONVERT(char(11),@x)```
Last edited by Brett Kaiser; 03-08-04 at 13:50.

3. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
If you just need the rounding, then check the round function. If you need zero padding too (which is rather silly in most cases, since SQL Server handles all of the "leading zero" issues for you), then you'll probably have to resort to a user defined function.

-PatP

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Methinks thou meant:
PHP Code:
``` DECLARE @x decimal(15,5), @y char(11) SELECT @x = 800.75864  SELECT @x, RIGHT(REPLICATE('0',11)+CONVERT(varchar(11),CONVERT(decimal(15,2),@x)),11)  ```
That works, as long as the value to convert is positive... Then you get some "interesting" results as the sign floats about!

-PatP

5. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Yeah...what an idiot....

char(11)...

just found the prob....

Thanks...

6. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
It is the "little" things that make the biggest boom when you trip over them!

-PatP

7. Registered User
Join Date
Mar 2004
Posts
45
declare @n numeric(19,5)
set @n = 800.75864
SELECT REPLACE(STR(@n-.005,11,2),' ','0')

Hans.

8. Registered User
Join Date
Feb 2004
Location
Posts
46
THANKS A LOT! IT WORKED

9. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Originally posted by diegocro
THANKS A LOT! IT WORKED
What did you expect?

And which one did you use?

10. Registered User
Join Date
Feb 2004
Location
Posts
46
Both, I've been trying to do this since yestarday, that's why I was happy.

11. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Well that'll learn ya

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•