Удалить ненужные символы в поле MS SQL 2012

#sql #sql_server

Есть таблица с колонками (Column1, Column2, Column3, Column4).

Column4 принимает значения 'dasf895580000000', 'dasf8955sf8000sf0000', 'dasf8955800sf00000
абв', 'абв dasf89558000000sf0sf', '89558000000'.

Как удалить всё кроме цифр?
Такая проблема встречается очень часто, как создать функцию по удалению ненужных
символов в поле?
    

Ответы

Ответ 1

Покажу вариант без функций (оставить только цифры) по одному case на каждую цифру.

select Column1, Column2, Column3, 
case when len(Column4)>0 and isnumeric(substring(Column4,1,1))=1 then substring(Column4,1,1)
else '' end +
case when len(Column4)>1 and isnumeric(substring(Column4,2,1))=1 then substring(Column4,2,1)
else '' end +
case when len(Column4)>2 and isnumeric(substring(Column4,3,1))=1 then substring(Column4,3,1)
else '' end +
case when len(Column4)>3 and isnumeric(substring(Column4,4,1))=1 then substring(Column4,4,1)
else '' end +
case when len(Column4)>4 and isnumeric(substring(Column4,5,1))=1 then substring(Column4,5,1)
else '' end +
case when len(Column4)>5 and isnumeric(substring(Column4,6,1))=1 then substring(Column4,6,1)
else '' end +
case when len(Column4)>6 and isnumeric(substring(Column4,7,1))=1 then substring(Column4,7,1)
else '' end +
case when len(Column4)>7 and isnumeric(substring(Column4,8,1))=1 then substring(Column4,8,1)
else '' end +
case when len(Column4)>8 and isnumeric(substring(Column4,9,1))=1 then substring(Column4,9,1)
else '' end +
case when len(Column4)>9 and isnumeric(substring(Column4,10,1))=1 then substring(Column4,10,1)
else '' end +
case when len(Column4)>10 and isnumeric(substring(Column4,11,1))=1 then substring(Column4,11,1)
else '' end  num
from table1


Ответ 2

Такое, на мой взгляд, лучше унести в CLR-функцию. На SQL тоже можно, но скорее всего
будет не слишком производительно.

Функция:

create function tfDigitsOnly
(
    @value nvarchar(100)
)
returns table
as return
    with tally as (select top (100) N=row_number() over (order by @@spid) from sys.all_columns)
    select p.value
    from (values (@value)) v(value)
        cross apply (
            select (select C + ''
            from (select N, substring(v.value, N, 1) C from tally where N <= datalength(v.value)
/ 2) [1]
            where C between N'0' and N'9'
            order by N
            for xml path(''))
        ) p (value)
GO


(Модифицировал код, взятый отсюда)

Пример использования:

declare @table table (Column4 nvarchar(100))
insert into @table values
    (N'dasf895580000000'),
    (N'dasf8955sf8000sf0000'),
    (N'dasf8955800sf00000 абв'),
    (N'абв dasf89558000000sf0sf'),
    (N'89558000000');

select d.value as Column4_CleanedUp
from @table t
    cross apply tfDigitsOnly(t.Column4) d;


Если символы кроме цифр представляют собой мусор, то хорошо было бы избавляться от
них, ещё до попадания данных в БД. Если же символы преследуют цель форматирования (хранение
номеров телефонов, например), то, мне кажется форматирование лучше унести во front-end,
а в БД хранить лишь цифры.


Ответ 3

С помощью функции

Добавьте функцию, которая просто будет в цикле перебирать и фильтровать цифры.

CREATE FUNCTION dbo.GetDigits(@string nvarchar(4000))
RETURNS nvarchar(4000)
AS
BEGIN
    DECLARE @i int = 0;
    DECLARE @result nvarchar(4000) = '';
    DECLARE @char nvarchar(1);

    WHILE (@i < LEN(@string))
    BEGIN
        SET @char = SUBSTRING(@string, @i, 1);

        IF (@char >= N'0' AND @char <= N'9')
            SET @result += @char;

        SET @i += 1;
    END

    RETURN @result;
END;


Затем пример вызова это функции (для демонстрации)

SELECT dbo.GetDigits(a)
FROM 
(VALUES (N'dasf895580000000'),
    (N'dasf8955sf8000sf0000'),
    (N'dasf8955800sf00000 абв'),
    (N'абв dasf89558000000sf0sf'),
    (N'89558000000')) AS X(a);


Также можно создать PERSISTED Computed Column для этих целей, где будет вызываться
функция GetDigits.

Одним запросом

SELECT digits
FROM 
-- Примеры значений
(VALUES (N'dasf895580000000'),
    (N'dasf8955sf8000sf0000'),
    (N'dasf8955800sf00000 абв'),
    (N'абв dasf89558000000sf0sf'),
    (N'89558000000')) AS T(string)
CROSS APPLY
(SELECT STUFF( -- Используем для конкатенации строк
    (SELECT SUBSTRING(string, number, 1)
    FROM master.dbo.spt_values -- Для выборки диапазона цифр от 0 до длины строки
    WHERE type = 'P' 
        AND number < LEN(string)
        AND SUBSTRING(string, number, 1) BETWEEN N'0' AND N'9' -- Фильтруем только цифры
    FOR XML PATH (''), TYPE) -- Используем для конкатенации строк
.value('text()[1]', 'nvarchar(max)'), 1, 2, '') -- Используем для конкатенации строк
AS digits) A;


Ответ 4

Рекурсивный вариант через табличное выражение одним запросом:

CREATE TABLE #SomeTable
(
    Id  INT,
    Field   VARCHAR(32),
    TestField   VARCHAR(32)
)

INSERT #SomeTable(Id, Field)
VALUES
(1, '12fasdf453456dsfg343'),
(2, '23343455'),
(3, 'sdasdfagds'),
(4, 'fadshfhh234asd3344'),
(5 ,''),
(6, '2312fadshfhh234asd'),
(7 ,'1'),
(8 ,'g')

;WITH cte
AS
(
    SELECT v.Id AS Id, CAST(SUBSTRING(v.Field, PATINDEX('%[0123456789]%', v.Field),
1) AS VARCHAR(32))AS symbol, 
        SUBSTRING(v.Field, PATINDEX('%[0123456789]%', v.Field) + 1, LEN(v.Field)
- PATINDEX('%[0123456789]%', v.Field)) AS substr
    FROM #SomeTable v
    UNION ALL
    SELECT cte.Id, CAST(CONCAT(symbol, SUBSTRING(substr, PATINDEX('%[0123456789]%',
substr), 1)) AS VARCHAR(32)), 
        SUBSTRING(substr, PATINDEX('%[0123456789]%', substr) + 1, LEN(substr) - PATINDEX('%[0123456789]%',
substr))
    FROM cte 
        JOIN #SomeTable v ON v.Id = cte.Id
    WHERE PATINDEX('%[0123456789]%', substr) > 0    
)
UPDATE v
SET TestField = cte.symbol
FROM cte
    JOIN #SomeTable v ON v.Id = cte.Id
WHERE PATINDEX('%[0123456789]%', substr) = 0

SELECT *
FROM #SomeTable

DROP TABLE #SomeTable