August 7, 2012 at 11:06 am
I am trying to write a SQL statement to pad numbers with 0's. The number string has a . delimiter and I need to add 0's to the front of each string. The string does not have the same amount of numbers before and after the . delimiter. I need a 7.7 format. Examples below.
Current Code -->> Code needed
0008.22816 -->> 0000008.0022816
069.3142 -->> 0000069.0003142
Can someone assist please?
August 7, 2012 at 11:18 am
Something like this?
with SampleData as ( -- this cte simply provides the sample data for the query
select
strval
from
(values ('0008.22816'),('069.3142'))dt(strval))
select
strval,
right('0000000' + substring(strval,1,charindex('.',strval) - 1), 7) +
'.' +
right('0000000' + substring(strval,charindex('.',strval) + 1,len(strval) - charindex('.',strval)), 7)
from
SampleData;
August 7, 2012 at 11:27 am
This?
DECLARE @tab TABLE
(
Value DECIMAL (14,7)
)
INSERT INTO @tab (Value)
SELECT 0008.22816 -->> 0000008.0022816
UNION ALL SELECT 069.3142 -->> 0000069.0003142
SELECT T.Value
,OtrApp.ConvVal
,[7.7 Format]= RIGHT(('0000000'+CrsApp.Whole) , 7) + '.' + RIGHT (('0000000'+CrsApp.Frac) , 7)
FROM @tab T
OUTER APPLY ( SELECT CONVERT(VARCHAR(15),T.Value)) OtrApp (ConvVal)
CROSS APPLY (SELECT LEFT (OtrApp.ConvVal , CHARINDEX('.',OtrApp.ConvVal)-1)
,STUFF(OtrApp.ConvVal,1,CHARINDEX('.',OtrApp.ConvVal),'')
) CrsApp (Whole,Frac)
August 7, 2012 at 11:43 am
Thanks. Worked like a Gem.
August 8, 2012 at 12:14 am
Here is one more
with t(v) as ( select '0008.22816' union all select '069.3142' )
select
[formattted] =
replace(str(parsename(t.v,2),7),' ','0') + '.' +
replace(str(parsename(t.v,1),7),' ','0'),
*
from t
Also, be aware that all presented solutions will fail or loose data if there will be more than 7 digits.
To avoid it some extra moves should be done.
August 8, 2012 at 11:38 pm
This should have no length limitations since the result is a string.
DECLARE
@s-2 VARCHAR(50)
,@Split CHAR(1)
,@X XML
,@strNewValue VARCHAR(50)
SET @s-2 = 0008.22816 -->> 0000008.0022816
SET @s-2 = 069.3142 -->> 0000069.0003142
SET @Split = '.'
--split the two parts into separate elements using the XML reader
SELECT @X = CONVERT(XML,'<root><s>' + REPLACE(@S,@Split,'</s><s>')+'</s></root>')
SELECT
--recombine the elements
@strNewValue = COALESCE(@strNewValue + @Split,'')
+ CAST(Result.PaddedValue AS VARCHAR(MAX))
FROM
(
SELECT
--add the padding
REPLICATE('0',7-LEN(CAST(Value AS INT)))+Value AS PaddedValue
FROM
(
--make some table rows
SELECT T.c.value('.','VARCHAR(MAX)') AS [Value] FROM @X.nodes('/root/s') T (c)
) Element
) Result
SELECT
@strNewValue AS NewValue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply