I have a money field which is 10 in character

The requirement is for example

-365.67. 

should come out

-0000036589

presently my result is 

0000-36589 because I am using the following

	+ convert(char(10), replicate('0',10-len(replace(convert(varchar,t.AllowedAmount),'.','')))
	         +replace(convert(varchar,t.AllowedAmount),'.',''))

 which adds 0000000000 to the value 

How do I work around this to me give the desired result.

-0000036589

Thanks

Try something like this

CREATE TABLE test1(a DECIMAL(5,2))

INSERT INTO test1 VALUES(-365.67)

SELECT * FROM test1

 SELECT CASE  WHEN a < 0

THEN '-' ELSE '' END + RIGHT('000000000' + REPLACE(a,'-',''), 11)

 FROM test1

Try this

declare @id varchar(10)='-365.67'
SELECT '-'+replace(replace(RIGHT('0000000000'+ @id,10),'-',''),'.','')

--Prashanth

create table test(AllowedAmount money)
Insert into test values(-365.67), (0),(365.67)
select AllowedAmount 
,Case when AllowedAmount<0 Then '-' Else '' End + Right('0000000000'+Replace( Cast (abs(AllowedAmount) as varchar(10)),'.',''),10) 
from test

drop table test

• Proposed as answer by Eric__ZhangMicrosoft contingent staff, Moderator Saturday, February 28, 2015 12:23 PM
• Marked as answer by Eric__ZhangMicrosoft contingent staff, Moderator Friday, March 06, 2015 6:20 AM

The below example works but returns a character length of 11 when a negative value appears hear by 
shifting the final output to exceed the expected number of bytes how can this be modified to have 10 characters for positive integer as well as 10 character length for a negative integer.Thanks
create table test(AllowedAmount money)
Insert into test values(-365.67), (0),(365.67)
select AllowedAmount 
,Case when AllowedAmount<0 Then '-' Else '' End + Right('0000000000'+Replace( Cast (abs(AllowedAmount) as varchar(10)),'.',''),10) 
from test

drop table test

This?

create table test(AllowedAmount money)
Insert into test values(-365.67), (0),(365.67)
select AllowedAmount 
,Case when AllowedAmount<0 Then '-' Else '' End + Right('0000000000'+Replace( Cast (abs(AllowedAmount) as varchar(10)),'.',''),10) 
 ,Stuff (Right('0000000000'+Replace( Cast (abs(AllowedAmount) as varchar(10)),'.',''),10) ,1,1,Case when AllowedAmount<0 Then '-' Else '' End )
 ,Len(Stuff (Right('0000000000'+Replace( Cast (abs(AllowedAmount) as varchar(10)),'.',''),10) ,1,1,Case when AllowedAmount<0 Then '-' Else ' ' End ))
from test

drop table test

Thank you 

Please check out this:

create table test(AllowedAmount money)
Insert into test values(-365.67), (0),(365.67)
select AllowedAmount 
--,Case when AllowedAmount<0 Then '-' Else '' End + Right('0000000000'+Replace( Cast (abs(AllowedAmount) as varchar(10)),'.',''),10) 
-- ,Stuff (Right('0000000000'+Replace( Cast (abs(AllowedAmount) as varchar(10)),'.',''),10) ,1,1,Case when AllowedAmount<0 Then '-' Else '' End )
-- ,Len(Stuff (Right('0000000000'+Replace( Cast (abs(AllowedAmount) as varchar(10)),'.',''),10) ,1,1,Case when AllowedAmount<0 Then '-' Else ' ' End ))

 ,Format(AllowedAmount,'0000000000')
 ,len(Format(AllowedAmount,'0000000000'))
from test


drop table test

So is there any way the positive values remains a character/count of 10 rather than 9 because I want it to be even at the end of the field.

So I guess this will be the best script to use right?

The reason I show you the FORMAT is what yo are asking for is not a standard way. I included the ugly code in my previous posting to you. The new format function is very clean: 

Format(AllowedAmount,'0000000000')

Using the method still creates a difference in character length between positive and negative values .

Because In am concatenating different columns it forces the total byte to exceed the expected byte

create table test(AllowedAmount money)
Insert into test values(-365.67), (0),(365.67)
select AllowedAmount 

Desired output example 365.67    -------- 0000036567   Dre Lawssonnnn

-                                  - 365.67    -------- -000036567   Dre Lawssonnnn

                                                even character length

rather than   which is my current output

                                     365.67    -------- 0000036567   Dre Lawssonnnn

-                                 - 365.67    -------- -0000036567   Dre Lawssonnnn

Desired output example 365.67    -------- 0000036567   Dre Lawssonnnn

-                                     -365.67    -------- -000036567   Dre Lawssonnnn

                                                even character length

rather than   which is my current output

                                     365.67    -------- 0000036567   Dre Lawssonnnn

-                                 - 365.67    -------- -0000036567   Dre Lawssonnnn

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You got everything wrong.  You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. 

>> I have a money [wrong!] field [sic] which is CHAR(10) <<

Columns are not fields; this is a fundamental concept in RDBMS. Then MONEY is a proprietary data type in T-SQL. Unfortunately, it does not do correct math! Google it, so you will know to avoid it.  Finally, we use DECIMAL(s,p) for currency in SQL.

>> The requirement is for example
-365.67. 
should come out
-0000036589 ...
How do I work around this to me give the desired result. <<

You are writing 1960's COBOL in T-SQL.. Or maybe 1960's BASIC with all those string functions. This is not SQL! What are you trying to do? And to make it worse, you used the old Sybase CONVERT () and not the ANSI/ISO Standard CAST(). 

Why did you do this? I have to teach SQL to people, so I need to know what they are thikign when they make such serious errors. Please tell me.