How do I pad a delimited number?

  • 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?

  • 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;

  • 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)

  • Thanks. Worked like a Gem.

  • 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[/url]
    Twitter: @SomewereSomehow

  • 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply