SQL Server BASE Function

Updated 2023-10-13 19:46:01.020000

Description

Use the scalar function BASE to generate a text representation of a number for a given radix (base).

Syntax

SELECT [westclintech].[wct].[BASE] (
   <@Number, bigint,>
  ,<@Radix, int,>
  ,<@MinLength, int,>)

Arguments

@Number

The number to be converted. Must be of a type bigint or of a type that implicitly converts to bigint.

@Radix

The radix (base) used to generate the text representation.

@MinLength

The minimum length of the text representation.

Return Type

nvarchar(4000)

Remarks

@Number > 0.

2 = @radix = 36.

If @Radix IS NULL then @radix = 10.

If @MinLength IS NULL then @MinLength = 1

1 = @MinLength = 255.

Examples

Example #1

In this examples we convert the number 255 into its hexadecimal representation.

SELECT wct.BASE(255, 16, NULL) as BASE;

This produces the following result.

Example #2

In this example we convert all the numbers from 0 to 255 to all the bases from 2 to 36 and PIVOT the resultant table.

SELECT number,
       cast([2] as nchar(8)) as [2],
       cast([3] as nchar(8)) as [3],
       cast([4] as nchar(8)) as [4],
       cast([5] as nchar(8)) as [5],
       cast([6] as nchar(8)) as [6],
       cast([7] as nchar(8)) as [7],
       cast([8] as nchar(8)) as [8],
       cast([9] as nchar(8)) as [9],
       cast([10] as nchar(8)) as [10],
       cast([11] as nchar(8)) as [11],
       cast([12] as nchar(8)) as [12],
       cast([13] as nchar(8)) as [13],
       cast([14] as nchar(8)) as [14],
       cast([15] as nchar(8)) as [15],
       cast([16] as nchar(8)) as [16],
       cast([17] as nchar(8)) as [17],
       cast([18] as nchar(8)) as [18],
       cast([19] as nchar(8)) as [19],
       cast([20] as nchar(8)) as [20],
       cast([21] as nchar(8)) as [21],
       cast([22] as nchar(8)) as [22],
       cast([23] as nchar(8)) as [23],
       cast([24] as nchar(8)) as [24],
       cast([25] as nchar(8)) as [25],
       cast([26] as nchar(8)) as [26],
       cast([27] as nchar(8)) as [27],
       cast([28] as nchar(8)) as [28],
       cast([29] as nchar(8)) as [29],
       cast([30] as nchar(8)) as [30],
       cast([31] as nchar(8)) as [31],
       cast([32] as nchar(8)) as [32],
       cast([33] as nchar(8)) as [33],
       cast([34] as nchar(8)) as [34],
       cast([35] as nchar(8)) as [35],
       cast([36] as nchar(8)) as [36]
FROM
(
    SELECT k.seriesValue as [Number],
           l.seriesvalue as Radix,
           wct.BASE(k.seriesValue, l.seriesvalue, 8) as BASE
    FROM wct.SeriesInt(0, 255, 1, NULL, NULL) k
        CROSS APPLY wct.SeriesInt(2, 36, 1, NULL, NULL) l
) p
PIVOT
(
    max(BASE)
    FOR RADIX in ([2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13],
              [14], [15], [16], [17], [18], [19],
                  [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30]
                            , [31], [32], [33], [34], [35], [36]
                 )
) d;

This produces the following result.