Does below work?
declare @myval DECIMAL(15,10)
set @myval = '02345.0000123245'
select format(@myval,'0000000000.00000000000000000000')
Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hi ,
Can somebody help me in writing the query to pad zeros for a decimal values.
At the moment it is with 00000.0000000000 ( with a length of 5.10 )
I want to write a statement to convert them to 0000000000.00000000000000000000 ( with a length of 10.20 )
Another example : 02345.0000123245 will change to 0000002345.00000000000000123245
Thanks in advance!!!
Does below work?
declare @myval DECIMAL(15,10)
set @myval = '02345.0000123245'
select format(@myval,'0000000000.00000000000000000000')
Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav
Hi @Ashwin Chithran ,
declare @myval DECIMAL(15,10)
set @myval = '02345.0000123245'
;with cte1
as(select format(@myval,'0000000000.0000000000') as col1)
,cte2
as (select col1,right(col1,len(col1)-charindex('.',col1 )) col2 from cte1)
select left(col1,charindex('.',col1 ))+left('00000000000000000000',20-len(col2))+col2 from cte2
If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.
Regards
Echo
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table
How about this:
DECLARE @myval DECIMAL(15, 10);
SET @myval = '02345.0000123245';
SELECT FORMAT(@myval, '0000000000.##################');
FORMAT is the easiest way, but may have a severe performance impact when used inside a heavy query.
This is because FORMAT is to be handled by the external .Net library, and the mere occurence of FORMAT within a query blocks some query optimizations.
The following solution (for non-negative numbers) uses only internal functions, and may be much more efficient:
DECLARE @myval DECIMAL(15, 10) = 02345.0000123245;
-- SELECT FORMAT(@myval, '0000000000.00000000000000000000');
SELECT right('0000000000' + convert(varchar(32), convert(decimal(30,20), @myval)), 31);