В сетевых конференциях по 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