So my data is delivered as numeric(9,2)...like 100.00. I have to extract that data and store it as a varchar 0 filled without the decimal place...like 0000010000

I tried the following and it did not work...

RIGHT('000000000'	+	CONVERT(VARCHAR,[EODPosting].[Amount]),10),

Can anyone help?

Thanks for your review and am hopeful for a reply.

You should always specify a size of your varchar

RIGHT('000000000'	+	CONVERT(VARCHAR(10),[EODPosting].[Amount]*100),10),

• Proposed as answer by Naomi NModerator 10 hours 53 minutes ago
• Unproposed as answer by Eric__ZhangMicrosoft contingent staff, Moderator 3 hours 59 minutes ago
• Proposed as answer by Naomi NModerator 3 hours 42 minutes ago
• Unproposed as answer by Naomi NModerator 3 hours 36 minutes ago

create table [EODPosting] ([Amount] decimal(9,2))

insert into [EODPosting] values(100.00),(567890.00)
select Replace(RIGHT('000000000'	+	CONVERT(VARCHAR(9),[EODPosting].[Amount]),10),'.','') 

from [EODPosting]


drop table [EODPosting]

• Proposed as answer by Eric__ZhangMicrosoft contingent staff, Moderator 3 hours 59 minutes ago
• Unproposed as answer by Eric__ZhangMicrosoft contingent staff, Moderator 3 hours 54 minutes ago

Hi,

Please try below options,

1. There are a dedicated function :  FLOOR() that will convert to whole number,

2. without converting the input to whole number.

Select RIGHT('000000000' + CAST(FLOOR(100.00)*100 as VARCHAR(10)),10)
RIGHT('000000000' + CAST(FLOOR([EODPosting].[Amount])*100 as VARCHAR(10)),10)

select Replace(RIGHT('000000000' + CONVERT(VARCHAR(9),100.00),10),'.','') 
Replace(RIGHT('000000000' + CONVERT(VARCHAR(9),[EODPosting].[Amount]),10),'.','') 

Hi Li,

ITBobP would like to return a 10-length string like "0000010000" as mentioned, so the REPLACE should process before the RIGHT. See the tweaked query.

create table [EODPosting] ([Amount] decimal(9,2))

insert into [EODPosting] values(100.00),(567890.00)
select RIGHT('0000000000'+REPLACE((CONVERT(VARCHAR(9),[EODPosting].[Amount])),'.',''),10) 

from [EODPosting]


drop table [EODPosting]

Try

declare @EODPosting table ([Amount] decimal(9,2))

insert into @EODPosting values(100.00),(567890.00)
select Amount, RIGHT('0000000000'+REPLACE((CONVERT(VARCHAR(9),Amount)),'.',''),10) as FirstSolution,
RIGHT('000000000'	+	CONVERT(VARCHAR(20),floor(Amount*100)),10) as SecondSolution

from @EODPosting