Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do I pad a delimited number? Expand / Collapse
Author
Message
Posted Tuesday, August 7, 2012 11:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 12, 2014 1:13 PM
Points: 53, Visits: 84
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?
Post #1341422
Posted Tuesday, August 7, 2012 11:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 22,993, Visits: 31,473
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;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1341428
Posted Tuesday, August 7, 2012 11:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:03 PM
Points: 2,262, Visits: 5,405
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)

Post #1341430
Posted Tuesday, August 7, 2012 11:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 12, 2014 1:13 PM
Points: 53, Visits: 84
Thanks. Worked like a Gem.
Post #1341443
Posted Wednesday, August 8, 2012 12:14 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:17 AM
Points: 75, Visits: 443
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.



I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru
Twitter: @SomewereSomehow
Post #1341690
Posted Wednesday, August 8, 2012 11:38 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
This should have no length limitations since the result is a string.

DECLARE
@S VARCHAR(50)
,@Split CHAR(1)
,@X XML
,@strNewValue VARCHAR(50)


SET @S = 0008.22816 -->> 0000008.0022816
SET @S = 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


Post #1342388
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse