Ниже будет показано, как конвертировать из любой системы величины к десятичному значению так, чтобы можно было применять любые арифметические обработки. В следующий раз я покажу, как конвертировать результат десятичной величины обратно в выбранную систему. Обычная проблема для запросов на T-SQL состоит в том, что приходится писать много логических обработок. Даже если в действительности нет необходимости в конверторе базы данных, написать конвертор на T-SQL - хорошая практика для шлифовки приемов работы на T-SQL. Отнеситесь к данной задаче как к упражнению на логику и попробуйте придумать собственное решение, прежде чем посмотреть ответ.

Преобразование в десятичную систему

Выбрана символьная строка, представляющая значение в некоторой системе счисления, и задача состоит в конвертировании ввода к десятичному значению типа данных bigint. Запустите сценарий, описанный в Листинге 1, для создания таблицы T1 и заполните ее тремя значениями, каждое в своей системе счисления. Поле id - это поле IDENTITY, которое выполняет функции первичного ключа, val - символьная строка, которая содержит значение, и база - это основание системы счисления. Необходимо написать код на T-SQL, который конвертирует все входные величины из T1 в данную базу десятичным значениям (десятичная система). В Таблице 1 показан желаемый вывод. Попробуйте придумать решение на основе набора, которое будет, вероятно, более эффективно, чем решение, основанное на итерационном методе.

Перед тем, как описывать собственное решение, я сначала объясню логику после конвертирования величин в данной системе к десятичному значению. Вводное десятичное значение v по базе b, которое содержит n символов - это SUM((первая цифра)× b0 + (вторая цифра) × b1 + (третья цифра) × b2 + ... + (n-ая цифра)t × b(n-1)), где первое число самое правое число, второе - вторая цифра справа, и так далее. Например, шестнадцатеричное значение (шестнадцатеричная система) 1F - это 15 × 160 + 1 × 161 = 15 + 16 = 31 в десятичной системе. Первое шестнадцатеричное число F (читаем справа налево) равно 15 в десятичной системе, и второе шестнадцатеричное число 1 равно 1 в десятичной системе.

Первый шаг в вычислении должен разделять каждое входное значение на отдельные цифры справа налево. Можно легко этого добиться, соединяя T1 со вспомогательной таблицей чисел, которая создается и заполняется, если выполнить код из Листинга 2. Используется условие связывания n <= LEN(val)

Напишите следующее выражение SELECT для извлечения отдельных цифр:

SUBSTRING(val,
LEN(val) - n + 1, 1)

Например, значение 1F создаст две результирующих строки: одну с n равно 1 и значением F, и другую с n равным 2 и значением 1.

Второй шаг немного хитрее. Теперь, когда вычислена числовая позиция (n) и извлечено число с этой позиции, вычисляем десятичное значение позиции. Можно выполнить это вычисление определением позиции символа извлеченного числа в пределах строки '0123456789ABCDE FGHIJKLMNOPQRSTUVWXYZ ' - 1. Например, число F является 16-м символом в вышеупомянутой строке; (16 - 1) дает его десятичное значение, 15. В T-SQL выражение для выполнения этого вычисления следующее:

CHARINDEX(SUBSTRING
(val, LEN(val) - n + 1, 1),
'0123456789ABCDEFGHIJKLM NOPQRSTUVWXYZ') - 1
AS decdigit

Фактически вы набираете строку символов как отдельную строку. Выполните запрос из Листинга 3, чтобы пройти предыдущие два шага, которые разбивают каждое входное значение на отдельные цифры и их соответствующие позиции и вычисляют десятичное значение каждой цифры. Таблица 2 показывает результаты этого запроса.

Третий шаг к решению проблемы - это умножение десятичного числа (decdigit) на основание в степени позиция числа минус 1: decdigit × основание(позиция-1). Четвертый и последний шаг должен группировать записи по id (группа по всем исходным значениям) и вычислять сумму результата вычислений, проделанных в третьем шаге.

Чтобы выполнить последние два шага, создайте вторичную таблицу по запросу из Листинга 3. Во внешнем запросе сгруппируйте данные по id, значению и системе и по каждой группе возвратите результат следующего выражения на T-SQL:

SUM(decdigit * POWER(CAST
(base AS bigint), pos-1))
AS decval

Основание хранится как обычный целочисленный тип данных int. Причина конвертирования основания к типу данных bigint состоит в том, что функция POWER() возвращает тот же самый тип данных, что и тип данных первого параметра. Если вы хотите обеспечивать десятичные значения до самого высокого возможного значения целого числа (максимальное значение bigint), первый параметр для функции POWER() должен быть типом данных bigint. В Листинге 4 показано готовое решение, которое выдает требуемый результат, содержащийся в Таблице 1.

Выделение логики

Если необходимо выделить логику преобразования, можно создать определяемую пользователем функцию (UDF), которая принимает исходное значение и основание как входящие значения и возвращает десятичное значение. В свойствах функции нет ничего особенного, кроме факта, что запрос выполняется только по таблице Nums. Вместо многократных вводных значений в таблице, похожей на T1, можно использовать только одно вводное значение и основание. Пользователь определяет результирующий запрос в операторе RETURN.

Выполните код из Листинга 5 и создайте функцию dbo.fn_basetodec (). Для проверки функции вызовите ее и используйте значение 1F и основание 16 в качестве вводных значений:

SELECT dbo.fn_basetodec
('1F', 16);

В результате получается десятичное значение 31.

Теперь, когда есть метод преобразования значения в любой заданной системе (вплоть до основания 36) к десятичному числу, необходимо выполнять арифметические операции над вводными значениями в любых данных системах. Например, чтобы вычислить результат добавления двух величин в двоичной системе, 1101 + 1010, просто выполните

SELECT dbo.fn_basetodec('1101', 2)
+ dbo.fn_basetodec('1010', 2)

Этот код выдает десятичный результат 23. Если нужно конвертировать результирующие десятичное значение в двоичную систему, можно написать программу, которая конвертирует значение из десятичной системы в двоичную систему или, еще лучше, в любую требуемую систему. В следующей статье я покажу, как написать такой конвертер.


Листинг 1. Создание и заполнение таблицы T1
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('T1') IS NOT NULL
  DROP TABLE T1;
GO
CREATE TABLE T1
(  id  NOT NULL IDENTITY PRIMARY KEY,
  val varchar(63) NOT NULL,
  base int  );
INSERT INTO T1 VALUES('STELLAARTOIS', 36);
INSERT INTO T1 VALUES('1F', 16);
INSERT INTO T1 VALUES('1011', 2);

 

Листинг 2. Создание и заполнение вспомогательной таблицы Nums
IF OBJECT_ID('Nums') IS NOT NULL DROP TABLE Nums;
CREATE TABLE Nums(n int NOT NULL);
SET NOCOUNT ON;
DECLARE @max AS int, @rc AS int;
SET @max = 8000;
SET @rc = 1;
 
BEGIN TRAN;
  INSERT INTO Nums VALUES(1);
  WHILE @rc * 2 <= @max
  BEGIN
    INSERT INTO Nums SELECT n + @rc FROM Nums;
    SET @rc = @rc * 2;
  END
  INSERT INTO Nums SELECT n + @rc FROM Nums
      WHERE n + @rc <= @max;
 
COMMIT TRAN;
 
ALTER TABLE Nums ADD PRIMARY KEY(n);

 

Листинг 3. Разделение значиний на цифры и вычисление десятичного значения.
SELECT id, val, base, n AS pos,
  SUBSTRING(val, LEN(val) - n + 1, 1) AS digit,
  CHARINDEX(SUBSTRING(val, LEN(val) - n + 1, 1),
    '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') - 1
         AS decval
FROM T1 JOIN Nums
  ON n <= LEN(val);

 

Листинг 4. Запрос, возвращающий сконвертированное число в десятичной системе
SELECT id, val, base,
  SUM(decdigit * POWER(CAST(base AS BIGINT), pos-1))
      AS decval
FROM (SELECT id, val, base, n AS pos,
        CHARINDEX(SUBSTRING(val, LEN(val) - n + 1, 1),
          '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') - 1
             AS decdigit
      FROM T1 JOIN Nums
        ON n <= LEN(val)) AS D
GROUP BY id, val, base;

 

Листинг 5. Функция, возвращающая сконвертированное число в десятичной системе
CREATE FUNCTION dbo.fn_basetodec
   (@val AS VARCHAR(63), @base AS int)
  RETURNS BIGINT
AS
BEGIN
  RETURN
    (SELECT SUM(
       (CHARINDEX(
          SUBSTRING(@val, LEN(@val) - n + 1, 1),
          '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') - 1)
       * POWER(CAST(@base AS BIGINT), n-1))
     FROM Nums
     WHERE n <= LEN(@val));
END
GO

 

Поделитесь материалом с коллегами и друзьями