A quick way to convert integer to nchar right aligned with leading zeroes

For an existing database table I urgently needed to translate integer values into nchar with leading zeroes

e.g. for an nchar destination column of length 2:

• convert value 1 to ‘01’
• convert value 2 to ‘02’
• convert value 11 to ‘11’

The simplest solution that came to my mind at that moment was to add an integer value that was a multiple of base 10 with enough trailing zeroes to act as leading zeroes in the nchar field. In cas of the nchar(4) column I need 10000, in case of nchar(2) I need to add 100.

• convert value (100 + 1) to ‘101’
• convert value (100 + 2) to ‘102’
• convert value (100 + 11) to ‘111’

The result has one character too much, therefore I need to subtract this first character sith the SUBSTRING function:

• SUBSTRING(‘101’, 2, 2) to ‘01’
• SUBSTRING(‘102’, 2, 2) to ‘02’
• SUBSTRING(‘111’, 2, 2) to ‘11’

T-SQL example: I have a dimension table Dim_YearMonth that needs to be populated with data from a Dim_Year and Dim_Month table. The table Dim_YearMonth has one nchar(7) column calle [YearMonthName] that is used as display value in dropdown lists. this has to be populated with datalike: ‘2014-01’, ‘2014-02’.

I have a table Dim_Year with one smallint column YearId ranging from 2005 to 2025.

There is a second table Dim_Month with smallint column MonthId ranging from 1 to 12.

Part of the T-SQL statement to combine year and month into ‘YYYY-MM’ is:

, CAST(y.YearID as nchar(4))+ '-' + SUBSTRING( CAST( (100+m.MonthId) as NCHAR(3)),2,2) as YearMonthName

The T-SQL statement to select Year and month combined and insert into the table Dim_YearMonth:

select (y.YearID *100 + m.MonthId) as YearMonthId

    , CAST(y.YearID as nchar(4))+ '-' + SUBSTRING( CAST( (100+m.MonthId) as NCHAR(3)),2,2) as YearMonthName

    , YearID, MonthId

    from dbo.Dim_Year as y, dbo.Dim_Month as m

The result looks like this:

YearMonthId    YearMonthName    CurrentYear    CurrentMonth
201308         2013-08          2013           8
201309         2013-09          2013           9
201310         2013-10          2013           10
201311         2013-11          2013           11
201312         2013-12          2013           12
201401         2014-01          2014           1
201402         2014-02          2014           2
201403         2014-03          2014           3