Help with leading 0s

  • I have a view whose resultset is captured and sent via bcp to a mainframe as a flat file. The file needs to have certain data formatted in a specific way. I need to place leading 0s in front of some fields and I'm not sure how to do this within my view. For example a value of $100 to be passed into a 10 byte field needs to be passed as 0000010000 (the mainframe is expecting the last 2 digits to be the cents, so i need no punctuation). Is there an easy way of creating a number of leading 0s based on the size of the expected output versus the size of the data length of each individual record returned within this view?

     

    Any help would be appreciated. Thanks in advance

    justin

  • it's for .NET 2.0/SQL 2005:

    create  a CLR function that would take a str like "$100' as a input parameter. Next:

    1.  process the str: get rid of $ and a dot; IF no dot - add 2 0's

    2. find the string length; compare it to the targeted 10-char string; add needed 0's

    3. return 10-char long string '0000010000'

    4. [compile; deploy assembly to a SQL server]

  • Hi Justin,

    Given that you're using SQL 2000 you cannot create a CLR function but you can do this easily enough using a standard user defined function that accepts an numeric value.

    Like so:

    create

    function dbo.fn_GetPaddedNumeric (@number numeric)

    returns varchar

    (10)

    as

    begin

    declare @result varchar(10)

    set @result = replace(convert(varchar(11),@number),'.','')

    while len(@result) < 10

    begin

    set @result = '0' + @result

    end

    return @result

    end

     

    Then, inside the view, you'd do:

    select col1, col2, dbo.fn_GetPaddedNumeric (col3)

    from my_table

     

    Col3 would be the columm you'd want to convert to having leading zero's.

    Hope that helps,

     

  • Karl, could I take it a step further and somehow code it to be used for varying lenghths? Some of my expected value lengths may be 10, but others are different, based on the data.

    Thanks for your response!

    Justin

  • Hi Justin,

    Yes, that would be quite easy. Just add another parameter called @length (datatype tinyint).

    Then, inside the function, the while statement can be:

    while len(@result) < @length

    When you call the function you'll just need to pass the length through to it.

  • DECLARE @Zeros TINYINT

    SELECT @Zeros = 10

    SELECT MyColumn,

           RIGHT(REPLICATE('0', @Zeros) + CONVERT(VARCHAR, CAST(100 * ROUND(MyColumn, 2) AS INT)), @Zeros)

    FROM   MyTable

     


    N 56°04'39.16"
    E 12°55'05.25"

  • declare @m money

    set @m =100

    select replace(STR(@m*100), space(1), '0')

     

    _____________
    Code for TallyGenerator

  • The simpliest example of leading zero creation I know of is:

     

    select

    replicate('0',@MaxLen-len(cast(MnyVal as varchar(10)))+cast(MnyVal as varchar(10))

    from TblOne

     

    Assuming that MnyVal is type money and that the varchar length is adequate.

    If MnyVal was already a char of some type then the statement becomes the even simplier:

    select replicate('0',@MaxLen-len(MnyVal))+MnyVal

    from TblOne

    Good hunting

     

  • Just a note about building CLR functions. T-SQL is still the native language of SQL Server and processing T-SQL functions is a lot quicker than CLR functions as SQL Server doesn't have to outsource the processing of the function to the CLR engine. CLR isn't meant as a replacement for T-SQL. It's meant as a supplement. T-SQL should be your first option in cases where it could be done in both.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Are you sure your solution is simpler than mine?

    Can you make the length of returned string to be changed from 10 to, say, 8 without rewriting script, just by supplying a parameter?

    _____________
    Code for TallyGenerator

Viewing 10 posts - 1 through 9 (of 9 total)

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