SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
jwalker8680
jwalker8680
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 118
Comments posted to this topic are about the item Function to Add Leading 0's (Zeros) to an INT
Todd Payne
Todd Payne
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 195
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
jwalker8680
jwalker8680
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 118
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...
sang-lee
sang-lee
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 48
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)
Todd Payne
Todd Payne
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 195
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search