Function to Add Leading 0's (Zeros) to an INT

  • Comments posted to this topic are about the item Function to Add Leading 0's (Zeros) to an INT

  • I use the power function to accomplish this task.

    SELECT RIGHT(power(10,@TotalLength) + @myInt,@TotalLength)

    I did use

    SELECT RIGHT(Convert(varchar,(power(10,@TotalLength) + @myInt)),@TotalLength)

    but found that I could eliminate the convert function from the statement.

    Thanks

    Todd P Payne

  • I could have taken the function down to a single line as well:

    RETURN RIGHT(replicate('0',@TotalLength) + CAST(@Int AS VARCHAR),@TotalLength)

    or.. SELECT RIGHT(replicate('0',6) + '1234',6)

    ...And I could use the code above instead of calling the function... but I think the way of using the function is much cleaner and keeps the code that uses it much cleaner.

    In your example... if you need it longer (25 in this example)... SELECT RIGHT(power(10,25) + 123456789,25)... <-- This will fail... Mine won't.

    Additionally, not having this in a function would make the example on my post extreamly long... For one off's... Yeah... you could code it right there, but if you need several items in a select converted to having leading 0's it would be tedious, messy and long...

    There is more than one way to skin this cat, but this is my preference...

  • Just thought that I would add to the discussion...I am no DBA....but forced to work in this area....so...pardon any bad code. I'm starting to write a stored procedure that will generate a US calendar based on the parameters specified...here it is so far...I have only done a brief validation of it...

    It's something to look at and if you have a better way to do it, that would be great...

    use ReportingDB

    truncate table dimcalendarrevenue

    declare @startdate date

    declare @enddate date

    declare @firstdayofmonth int

    declare @firstdayofweek int

    declare @tablename nvarchar(100)

    declare @quartername nvarchar(100)

    if @startdate < '01/01/1971'

    begin

    set @startdate = '01/01/1971'

    end

    else set @startdate = '01/01/2000'

    set @enddate = '12/31/2020'

    set @firstdayofmonth = 19

    set @firstdayofweek = 5 --week day numbers are (1 = monday, 2 = tuesday, 3 = wednesday, 4 = thursday, 5 = friday, 6 = saturday, 7 = sunday)

    set datefirst @firstdayofweek

    set @tablename = 'dimcalendarrevenue'

    set @quartername = 'Q'

    while @startdate <= @enddate

    begin

    insert into DimCalendarRevenue

    ------------------------------------------------------------

    -- regular calendar --

    ------------------------------------------------------------

    select convert(int,(right(replicate('0',4) + convert(nvarchar(4),datepart(yyyy,@startdate)),4) +

    right(replicate('0',2) + convert(nvarchar(2),datepart(mm,@startdate)),2) +

    right(replicate('0',2) + convert(nvarchar(2),datepart(dd,@startdate)),2))) as datekey,

    @startdate as [calendardate],

    year(@startdate) as [calendaryear],

    datepart(dy,@startdate) as [calendardayofyear],

    month(@startdate) as [calendarmonth],

    day(@startdate) as [calendardayofmonth],

    datepart(wk,@startdate) as [calendarweekofyear],

    datename(weekday,@startdate) as [calendarweekdayname],

    datepart(dw,@startdate) as [calendarweekdaynumber],

    datename(mm,@startdate) as [calendarmonthname],

    case

    when month(@startdate) in (1,2,3)

    then '1'

    when month(@startdate) in (4,5,6)

    then '2'

    when month(@startdate) in (7,8,9)

    then '3'

    when month(@startdate) in (10,11,12)

    then '4'

    end as [calendarquarter],

    @quartername + CONVERT(nvarchar(1), case

    when month(@startdate) in (1,2,3)

    then '1'

    when month(@startdate) in (4,5,6)

    then '2'

    when month(@startdate) in (7,8,9)

    then '3'

    when month(@startdate) in (10,11,12)

    then '4'

    end) as [calendarquartername],

    -----------------------------------------------------------

    -- adjusted calendar --

    -----------------------------------------------------------

    case

    when datepart(mm,@startdate) = 1

    and @firstdayofmonth <> 1

    then case

    when datepart(dd,@startdate) < @firstdayofmonth

    then year(dateadd(yy,-1,@startdate))

    else year(@startdate)

    end

    else year(@startdate)

    end as [adjustedyear],

    datepart(dy,dateadd(dd,-@firstdayofmonth + 1,@startdate)) as [adjusteddayofyear],

    case

    when @firstdayofmonth <> 1

    then case

    when datepart(dd,@startdate) < @firstdayofmonth

    then month(dateadd(mm,-1,@startdate))

    else month(@startdate)

    end

    else month(@startdate)

    end as [adjustedmonth],

    case

    when (day(@startdate) < @firstdayofmonth)

    and @firstdayofmonth <> 1

    then datediff(dd,dateadd(mm,-1,dateadd(dd,@firstdayofmonth-day(@startdate),@startdate)),@startdate) + 1

    when (day(@startdate) > @firstdayofmonth)

    and @firstdayofmonth <> 1

    then day(@startdate) - @firstdayofmonth + 1

    when day(@startdate) = @firstdayofmonth

    then 1

    else day(@startdate)

    end as [adjusteddayofmonth],

    case

    when datepart(wk,@startdate) = 53

    then 1

    else datepart(wk,@startdate)

    end as [adjusted52weeks],

    ceiling(cast(datepart(dy,dateadd(dd,-@firstdayofmonth + 1,@startdate)) as float)/7) as [adjustedweekofyear],

    datename(weekday,@startdate) as [adjustedweekdayname],

    datename(mm,dateadd(dd,-@firstdayofmonth+1,@startdate)) as [adjustedmonthname],

    case

    when (case

    when @firstdayofmonth <> 1

    then case

    when datepart(dd,@startdate) < @firstdayofmonth

    then month(dateadd(mm,-1,@startdate))

    else month(@startdate)

    end

    else month(@startdate)

    end) in (1,2,3)

    then 1

    when (case

    when @firstdayofmonth <> 1

    then case

    when datepart(dd,@startdate) < @firstdayofmonth

    then month(dateadd(mm,-1,@startdate))

    else month(@startdate)

    end

    else month(@startdate)

    end) in (4,5,6)

    then 2

    when (case

    when @firstdayofmonth <> 1

    then case

    when datepart(dd,@startdate) < @firstdayofmonth

    then month(dateadd(mm,-1,@startdate))

    else month(@startdate)

    end

    else month(@startdate)

    end) in (7,8,9)

    then 3

    when (case

    when @firstdayofmonth <> 1

    then case

    when datepart(dd,@startdate) < @firstdayofmonth

    then month(dateadd(mm,-1,@startdate))

    else month(@startdate)

    end

    else month(@startdate)

    end) in (10,11,12)

    then 4

    end as [adjustedquarter],

    @quartername + CONVERT(nvarchar(1),case

    when (case

    when @firstdayofmonth <> 1

    then case

    when datepart(dd,@startdate) < @firstdayofmonth

    then month(dateadd(mm,-1,@startdate))

    else month(@startdate)

    end

    else month(@startdate)

    end) in (1,2,3)

    then 1

    when (case

    when @firstdayofmonth <> 1

    then case

    when datepart(dd,@startdate) < @firstdayofmonth

    then month(dateadd(mm,-1,@startdate))

    else month(@startdate)

    end

    else month(@startdate)

    end) in (4,5,6)

    then 2

    when (case

    when @firstdayofmonth <> 1

    then case

    when datepart(dd,@startdate) < @firstdayofmonth

    then month(dateadd(mm,-1,@startdate))

    else month(@startdate)

    end

    else month(@startdate)

    end) in (7,8,9)

    then 3

    when (case

    when @firstdayofmonth <> 1

    then case

    when datepart(dd,@startdate) < @firstdayofmonth

    then month(dateadd(mm,-1,@startdate))

    else month(@startdate)

    end

    else month(@startdate)

    end) in (10,11,12)

    then 4

    end) as [adjustedquartername],

    -----------------------------------------------------------

    -- date facts --

    -----------------------------------------------------------

    case

    when datename(weekday,@startdate) = 'sunday' or datename(weekday,@startdate) = 'saturday'

    then 'weekend'

    else 'weekday'

    end as [weekdaytype],

    0 as [holidayflag],

    '' as [holidaytype],

    '' as [holidayname]

    set @startdate = dateadd(dd,1,@startdate)

    end

    -----------------------------------------------------------

    -- Update Adjusted Weeks For Reporting --

    -----------------------------------------------------------

    update DimCalendarRevenue

    set AdjustedWeekOfYear = case

    when DateKey >= (select drc2.DateKey

    from DimCalendarRevenue drc2

    where drc2.AdjustedWeekOfYear = drc.AdjustedWeekOfYear

    and drc2.AdjustedYear = drc.AdjustedYear

    and drc2.calendarweekdaynumber = 1)

    then AdjustedWeekOfYear + 1

    else adjustedweekofyear

    end

    from DimCalendarRevenue drc

    update DimCalendarRevenue

    set AdjustedWeekOfYear = case

    when drc.AdjustedWeekOfYear = 53

    then 1

    else drc.adjustedweekofyear

    end

    from DimCalendarRevenue drc

    -----------------------------------------------------------

    -- Update Holidays --

    -----------------------------------------------------------

    -- New Year's Day

    update DimCalendarRevenue

    set HolidayFlag = 1,

    HolidayType = 'US Federal Holiday',

    HolidayName = 'New Year''s Day'

    where CalendarMonth = 1

    and CalendarDayOfMonth = 1

    -- Independence Day

    update DimCalendarRevenue

    set HolidayFlag = 1,

    HolidayType = 'US Federal Holiday',

    HolidayName = 'Independence Day'

    where CalendarMonth = 7

    and CalendarDayOfMonth = 4

    -- Veteran's Day

    update DimCalendarRevenue

    set HolidayFlag = 1,

    HolidayType = 'US Federal Holiday',

    HolidayName = 'Veteran''s Day'

    where CalendarMonth = 11

    and CalendarDayOfMonth = 11

    -- Christmas Day

    update DimCalendarRevenue

    set HolidayFlag = 1,

    HolidayType = 'US Federal Holiday',

    HolidayName = 'Christmas Day'

    where CalendarMonth = 12

    and CalendarDayOfMonth = 25

    -- Birthday of Martin Luther King, Jr.

    update DimCalendarRevenue

    set HolidayFlag = 1,

    HolidayType = 'US Federal Holiday',

    HolidayName = 'Birthday of Martin Luther King, Jr. (Martin Luther King Day)'

    from DimCalendarRevenue drc

    where drc.CalendarMonth = 1

    and drc.CalendarDayOfMonth = (select top 1 td.calendardayofmonth

    from (select top 3 drc2.calendardayofmonth

    from DimCalendarRevenue drc2

    where drc2.CalendarYear = drc.CalendarYear

    and drc2.CalendarMonth = 1

    and drc2.CalendarWeekDayName = 'Monday'

    order by drc2.CalendarDayOfMonth asc) td

    order by td.calendardayofmonth desc)

    -- Washington's Birthday

    update DimCalendarRevenue

    set HolidayFlag = 1,

    HolidayType = 'US Federal Holiday',

    HolidayName = 'Washington''s Birthday (Presidents'' Day)'

    from DimCalendarRevenue drc

    where drc.CalendarMonth = 2

    and drc.CalendarDayOfMonth = (select top 1 td.calendardayofmonth

    from (select top 3 drc2.calendardayofmonth

    from DimCalendarRevenue drc2

    where drc2.CalendarYear = drc.CalendarYear

    and drc2.CalendarMonth = 2

    and drc2.CalendarWeekDayName = 'Monday'

    order by drc2.CalendarDayOfMonth asc) td

    order by td.calendardayofmonth desc)

    -- Memorial Day

    update DimCalendarRevenue

    set HolidayFlag = 1,

    HolidayType = 'US Federal Holiday',

    HolidayName = 'Memorial Day'

    from DimCalendarRevenue drc

    where drc.CalendarMonth = 5

    and drc.CalendarDayOfMonth = (select top 1 drc2.calendardayofmonth

    from DimCalendarRevenue drc2

    where drc2.CalendarYear = drc.CalendarYear

    and drc2.CalendarMonth = 5

    and drc2.CalendarWeekDayName = 'Monday'

    order by drc2.CalendarDayOfMonth desc)

    -- Labor Day

    update DimCalendarRevenue

    set HolidayFlag = 1,

    HolidayType = 'US Federal Holiday',

    HolidayName = 'Labor Day'

    from DimCalendarRevenue drc

    where drc.CalendarMonth = 9

    and drc.CalendarDayOfMonth = (select top 1 drc2.calendardayofmonth

    from DimCalendarRevenue drc2

    where drc2.CalendarYear = drc.CalendarYear

    and drc2.CalendarMonth = 9

    and drc2.CalendarWeekDayName = 'Monday'

    order by drc2.CalendarDayOfMonth asc)

    -- Columbus Day

    update DimCalendarRevenue

    set HolidayFlag = 1,

    HolidayType = 'US Federal Holiday',

    HolidayName = 'Columbus Day'

    from DimCalendarRevenue drc

    where drc.CalendarMonth = 10

    and drc.CalendarDayOfMonth = (select top 1 td.calendardayofmonth

    from (select top 2 drc2.calendardayofmonth

    from DimCalendarRevenue drc2

    where drc2.CalendarYear = drc.CalendarYear

    and drc2.CalendarMonth = 10

    and drc2.CalendarWeekDayName = 'Monday'

    order by drc2.CalendarDayOfMonth asc) td

    order by td.calendardayofmonth desc)

    -- Thanksgiving Day

    update DimCalendarRevenue

    set HolidayFlag = 1,

    HolidayType = 'US Federal Holiday',

    HolidayName = 'Thanksgiving Day'

    from DimCalendarRevenue drc

    where drc.CalendarMonth = 11

    and drc.CalendarDayOfMonth = (select top 1 td.calendardayofmonth

    from (select top 4 drc2.calendardayofmonth

    from DimCalendarRevenue drc2

    where drc2.CalendarYear = drc.CalendarYear

    and drc2.CalendarMonth = 11

    and drc2.CalendarWeekDayName = 'Thursday'

    order by drc2.CalendarDayOfMonth asc) td

    order by td.calendardayofmonth desc)

  • I just read your response. You are definitely right about encapsulating the logic into a function. It does make things much cleaner, and it never occurred to me that I would need to pad more than 10 leading zeros to an integer. I guess that shows my level of experience.

    I also noticed that neither of our methods work with negative integers.

    Here is my solution to that problem. Please forgive the word wrap.

    ALTER Function [dbo].[DBA_fnAddLeadingZeros](@Int INT, @TotalLength INT)

    AS

    BEGIN

    DECLARE @Return VARCHAR(2000);

    --IF @TotalLength > 100 SELECT @TotalLength = 100

    IF @Int < 0

    BEGIN

    -- moves the - sign to the first charactor

    SELECT @Return = Replicate('0',@TotalLength) + CAST(ABS(@Int) AS VARCHAR);

    SELECT @Return = '-' + Right(@Return,@TotalLength -1);

    END

    ELSE

    BEGIN

    SELECT @Return = Replicate('0',@TotalLength) + CAST(@Int AS VARCHAR);

    SELECT @Return = Right(@Return,@TotalLength);

    END

    RETURN @return;

    END

    Thanks

    Todd

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

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