Ниже будет показано, как конвертировать из любой системы величины к десятичному значению так, чтобы можно было применять любые арифметические обработки. В следующий раз я покажу, как конвертировать результат десятичной величины обратно в выбранную систему. Обычная проблема для запросов на 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;GOIF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1;GOCREATE 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 decvalFROM T1 JOIN Nums ON n <= LEN(val);
Листинг 4. Запрос, возвращающий сконвертированное число в десятичной системе
SELECT id, val, base, SUM(decdigit * POWER(CAST(base AS BIGINT), pos-1)) AS decvalFROM (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 DGROUP BY id, val, base;
Листинг 5. Функция, возвращающая сконвертированное число в десятичной системе
CREATE FUNCTION dbo.fn_basetodec (@val AS VARCHAR(63), @base AS int) RETURNS BIGINTASBEGIN 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));ENDGO