Функция MS SQL для лексикографически сопоставимых IP-адресов

Question

Я нашел следующий код для заполнения IP-адресов нулями, чтобы сделать его лексикографически сопоставимым. Код работает Finde, но мне нужно, как скалярная функция в SQL. Можете ли вы преобразовать это в коде, чтобы создать скалярную функцию, которая делает то же самое? Мы используем SQL Server 2008 R2 на данный момент.

DECLARE @a as NVARCHAR(15)
SET @a = '2.18.4.14'

SELECT 
    @a = CASE 
        WHEN CHARINDEX('.',@a) < 4 
            THEN STUFF(@a,1,0,REPLICATE('0',4-CHARINDEX('.',@a,1))) 
        ELSE @a 
    END
, 
    @a = CASE 
        WHEN CHARINDEX('.',@a) < 8 
            THEN STUFF(@a,5,0,REPLICATE('0',8-CHARINDEX('.',@a, 5))) 
        ELSE @a 
    END
, 
    @a = CASE 
        WHEN CHARINDEX('.',@a) < 12 
            THEN STUFF(@a,9,0,REPLICATE('0',12-CHARINDEX('.',@a, 9))) 
        ELSE @a 
    END
, 
    @a = CASE 
        WHEN LEN(@a) < 15 
            THEN STUFF(@a,13,0,REPLICATE('0',15-LEN(@a))) 
        ELSE @a 
    END

SELECT @a

Можете ли вы объяснить, как работает этот код? Я понимаю, что делают CHARINDEX, STUFF и REPLICATE, но я не понимаю шагов, как IP-адрес @a изменяется шагами CASE. Это скалярное значение или таблица?

Answer 1

Лично я бы использовал функцию табличных значений, а не скалярную функцию. Функция табличного значения далеко выполняет скалярные функции, и если вы собираетесь работать с набором данных, то вы заметите значительное увеличение скорости.

Во-первых, это использует DelmitedSplit8k Джеффа Модена. Если, однако, вы используете SQL Server 2016, то вы можете использовать STRING_SPLIT (Я не знаю, какую версию вы используете). Тогда функция так же проста, как:

CREATE FUNCTION Lexicon_IP_fn (@IPAddress VARCHAR(15))
RETURNS TABLE AS
RETURN 
(
    SELECT CONVERT(varchar(15),STUFF((SELECT '.' + RIGHT('000' + DS.Item,3)
                                      FROM DelimitedSplit8K(@IPAddress, '.') DS
                                      FOR XML PATH('')),1,1,'')) AS Lexicon_IP
);

Вы можете вызвать функцию, используя CROSS APPLY, Например:

WITH VTE AS (
    SELECT *
    FROM (VALUES ('1.1.1.1'),('123.123.1.42'),('127.0.0.1')) V([IP]))
SELECT [IP], LIP.Lexicon_IP
FROM VTE
     CROSS APPLY Lexicon_IP_fn([IP]) LIP;

Который возвращает:

IP           Lexicon_IP
------------ ---------------
1.1.1.1      001.001.001.001
123.123.1.42 123.123.001.042
127.0.0.1    127.000.000.001