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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Joshua A. Walker » Function to Add Leading 0's (Zeros) to an INT...

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

Rate Topic

Display Mode

Topic Options

Author

Message

jwalker8680

Posted Friday, February 13, 2009 10:37 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 26, 2013 12:28 PM
Points: 30, Visits: 110

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

Post #657114

Todd Payne

Posted Thursday, March 05, 2009 7:46 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:03 AM
Points: 27, Visits: 179

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

Post #669233

jwalker8680

Posted Friday, March 06, 2009 6:38 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 26, 2013 12:28 PM
Points: 30, Visits: 110

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...

Post #670156

sang-lee

Posted Thursday, April 09, 2009 8:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 2:37 PM
Points: 4, Visits: 46

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)

Post #694022

Todd Payne

Posted Thursday, April 09, 2009 3:02 PM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:03 AM
Points: 27, Visits: 179

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

Post #694465

« Prev Topic | Next Topic »

Permissions