Выполнение преобразований системы счисления (исходники)

Источник: Windows IT Pro, #07/2006
Ицик Бен Ган

В сетевых конференциях по SQL Server часто встречается вопрос о том, как выполняется преобразование системы. То есть пользователь сохраняет значения как строковые величины, содержащие числа в данной системе и хочет конвертировать их в другую систему счисления. Как правило, пользователю нужно хранить значения в недесятичной системе, когда приложение работает с недесятичными величинами (например, серийные номера, представленные по базе 36, двоичные битовые образы, хранимые в двоичной системе). Десятичная система использует цифры от 0 до 9. N-размерная система, меньше чем 10, использует цифры от 0 до n - 1. Система больше чем 10 использует числа от 0 до 9 плюс алфавитные символы, начинающиеся с A. Например, шестнадцатеричная система использует цифры 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 и символы от A до F, где A представляет собой десятичное значение 10, B представляет 11, и так далее. Однако SQL Server не поддерживает работу cо значениями, выраженными в недесятичной системе. Таким образом, возникают трудности в связи с необходимостью хранения таких значений и выполнения арифметических манипуляций, вроде вычисления результата выражения 1101 + 1010 в двоичной системе.

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

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

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

Перед тем, как описывать собственное решение, я сначала объясню логику после конвертирования величин в данной системе к десятичному значению. Вводное десятичное значение v по базе b, которое содержит n символов - это SUM((первая цифра)× b 0 + (вторая цифра) × b 1 + (третья цифра) × b 2 + ... + ( 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

Страница сайта http://test.interface.ru
Оригинал находится по адресу http://test.interface.ru/home.asp?artId=4769