Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Игорь_UUS
Member

Откуда: г. Екатеринбург
Сообщений: 652
Добрый день.

Подскажите самый правильны способ, использую MSSQL2016
30 авг 19, 14:58    [21960623]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6697
Игорь_UUS,

Вы решили показывать фокусы при помощи SQL? Это вообще не является задачей SQL server.
30 авг 19, 15:41    [21960665]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Игорь_UUS
Member

Откуда: г. Екатеринбург
Сообщений: 652
Владислав Колосов,

Может не так выразился...

Мне нужно SELECT'ом по одному полю таблицы вытянуть только цифры исключив любые символы, например

"Phone:8(912)29-5555-5"
"Phone:8(912)295-55-55"

в итоге должен получить только 89122955555.
30 авг 19, 16:51    [21960716]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Игорь_UUS
Member

Откуда: г. Екатеринбург
Сообщений: 652
Владислав Колосов,

вопрос закрыт... спасибо за участие))
30 авг 19, 17:17    [21960736]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6697
Игорь_UUS,

я бы использовал CLR функцию на основе C# regex.Replace.

// Автор: Колосов В.В.
// Дата создания: 10.05.2017
// Назначение: SQL C# функция, возвращает значение найденной группы, соответствующей образцу регулярного выражения.
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlChars regex.Replace( SqlString input, SqlString pattern, SqlString replacement )

    {

        if (true == input.IsNull)
            return SqlChars.Null;

        Regex regex = new Regex(pattern.Value);

        return new SqlChars(regex.Replace(input.Value, replacement.Value));

    }

};


Использование:

select dbo.RegexReplace('Phone:8(912)29-5555-5','\D+','')
30 авг 19, 17:19    [21960738]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6697
Хм, комментарий неправильный.
Надо читать "заменяет значение".
30 авг 19, 17:21    [21960740]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
invm
Member

Откуда: Москва
Сообщений: 8739
declare @t table (s varchar(30));
insert into @t
values
 ('"Phone:8(912)29-5555-5"'),
 ('"Phone:8(912)295-55-55"');

select
 t.*, cast(l.x.query('item/digit/text()') as varchar(100))
from
 @t t cross apply
 (select cast(cast('' as xml).query('sql:column("t.s")') as varchar(100))) a(s) cross apply
 (select replace(a.s, '0', '<digit>0</digit>')) b(s) cross apply
 (select replace(b.s, '1', '<digit>1</digit>')) c(s) cross apply
 (select replace(c.s, '2', '<digit>2</digit>')) d(s) cross apply
 (select replace(d.s, '3', '<digit>3</digit>')) e(s) cross apply
 (select replace(e.s, '4', '<digit>4</digit>')) f(s) cross apply
 (select replace(f.s, '5', '<digit>5</digit>')) g(s) cross apply
 (select replace(g.s, '6', '<digit>6</digit>')) h(s) cross apply
 (select replace(h.s, '7', '<digit>7</digit>')) i(s) cross apply
 (select replace(i.s, '8', '<digit>8</digit>')) j(s) cross apply
 (select replace(j.s, '9', '<digit>9</digit>')) k(s) cross apply
 (select cast('<item>' + k.s + '</item>' as xml)) l(x);
30 авг 19, 17:49    [21960757]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
iap
Member

Откуда: Москва
Сообщений: 46833
Недавно же была такая тема!
Вот, опять ссылку дам: выборка чисел из строки

А в той теме invm давал решение через XML, если я правильно помню.
Но я по своей тупости в XML слабоват...
Поищите сами. Совсем недавно.
30 авг 19, 17:52    [21960760]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
iap
Member

Откуда: Москва
Сообщений: 46833
iap
Недавно же была такая тема!
Вот, опять ссылку дам: выборка чисел из строки

А в той теме invm давал решение через XML, если я правильно помню.
Но я по своей тупости в XML слабоват... Картинка с другого сайта.
Поищите сами. Совсем недавно.
О! А вот и оно! Решение от invm!
30 авг 19, 17:53    [21960762]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1041
Игорь_UUS
Владислав Колосов,

вопрос закрыт... спасибо за участие))
Ээ, так неинтересно, куда это вы?

Недавно соревновались с коллегами, как правильнее )

Мой вариант был таким:

DECLARE 
       @number           varchar(99) = ' 8 (912) 295-55-55 ',
       @number_processed varchar(99)

SET @number_processed = 
(
    SELECT
        SUBSTRING (@number, v.number, 1) 
    FROM 
        dbo.number AS v
    WHERE 
        v.number < LEN(@number)
        AND SUBSTRING (@number, v.number, 1) LIKE '[0-9]'
    FOR XML PATH('')
)

PRINT @number_processed
30 авг 19, 17:54    [21960763]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1041
Minamoto
Игорь_UUS
Владислав Колосов,

вопрос закрыт... спасибо за участие))
Ээ, так неинтересно, куда это вы?

Недавно соревновались с коллегами, как правильнее )

Мой вариант был таким:

DECLARE 
       @number           varchar(99) = ' 8 (912) 295-55-55 ',
       @number_processed varchar(99)

SET @number_processed = 
(
    SELECT
        SUBSTRING (@number, v.number, 1) 
    FROM 
        dbo.number AS v
    WHERE 
        v.number < LEN(@number)
        AND SUBSTRING (@number, v.number, 1) LIKE '[0-9]'
    FOR XML PATH('')
)

PRINT @number_processed

Вариант invm:


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 5 ms.

Мой вариант:

 SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
30 авг 19, 18:01    [21960769]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
msLex
Member

Откуда:
Сообщений: 6424
Minamoto
Minamoto
пропущено...
Ээ, так неинтересно, куда это вы?

Недавно соревновались с коллегами, как правильнее )

Мой вариант был таким:

DECLARE 
       @number           varchar(99) = ' 8 (912) 295-55-55 ',
       @number_processed varchar(99)

SET @number_processed = 
(
    SELECT
        SUBSTRING (@number, v.number, 1) 
    FROM 
        dbo.number AS v
    WHERE 
        v.number < LEN(@number)
        AND SUBSTRING (@number, v.number, 1) LIKE '[0-9]'
    FOR XML PATH('')
)

PRINT @number_processed


Вариант invm:


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 5 ms.
Мой вариант:

 SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.



3 ms при измерении времени запроса в SQL это где-то на уровне шумов

Прогоните оба варианта на бОльших объемах (пару сотен тысяч строк), это уже можно будет сравнивать
Ну и желательно поиграться с размером строк и их заполненностью цифрами.
30 авг 19, 18:06    [21960772]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1041
msLex
Minamoto
пропущено...

Вариант invm:


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 5 ms.
Мой вариант:

 SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.



3 ms при измерении времени запроса в SQL это где-то на уровне шумов

Прогоните оба варианта на бОльших объемах (пару сотен тысяч строк), это уже можно будет сравнивать
Ну и желательно поиграться с размером строк и их заполненностью цифрами.

да даже на 16 строках у варианта invm 16 CPU и 8 elapsed, на моем по прежнему 2 :)

На реальных данных я проверил, мой вариант на 15 тыс. строк:
 SQL Server Execution Times:
CPU time = 797 ms, elapsed time = 800 ms.

(без вычисления колонки:
 SQL Server Execution Times:
CPU time = 406 ms, elapsed time = 445 ms.

Вариант invm:
Msg 9416, Level 16, State 1, Line 40
XML parsing: line 1, character 10, hexadecimal digit expected
30 авг 19, 18:15    [21960776]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1041
Minamoto
Вариант invm:
Msg 9416, Level 16, State 1, Line 40
XML parsing: line 1, character 10, hexadecimal digit expected

На пустую строку так реагирует.
Убрал, получилось:

 SQL Server Execution Times:
CPU time = 3969 ms, elapsed time = 3976 ms.
30 авг 19, 18:29    [21960787]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Сруль.
Member

Откуда:
Сообщений: 112
Может, чего не понял, функция-то, несложная.
create function dbo.f_only_digits_in_jazz(@str nvarchar(1000))
returns nvarchar(1000)
as
begin

declare @i int,@str_out nvarchar(1000)

select @i=LEN(@str),@str_out=''

while(@i>0)
begin
	if ascii(RIGHT(@str,1)) between ascii('0') and ascii('9')
	set @str_out=RIGHT(@str,1)+@str_out
	
	set @str=LEFT(@str, LEN(@str)-1)
	set @i=@i-1
end

return @str_out
end
go

select dbo.f_only_digits_in_jazz('Phone:8(912)29-5555-5')
1 сен 19, 14:40    [21961465]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
aleks222
Member

Откуда:
Сообщений: 730
[quot Сруль.]Может, чего не понял, функция-то, несложная.
Это императивное убожество.
1 сен 19, 16:00    [21961477]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Сруль.
Member

Откуда:
Сообщений: 112
Насчёт убожеств, это не ко мне, я больше на результат.
Здесь есть конкретный баг.
Когда в строке замешан пробел, функция просто вылетает, т.к. длина считается неправильно.
Поэтому несколько исправленная версия.
alter function dbo.f_only_digits_in_jazz(@str nvarchar(1000))
returns nvarchar(1000)
as
begin

declare @i int,@str_out nvarchar(1000)

select @i=LEN(@str),@str_out=''
,@str=REPLACE(@str,' ','~')--против пробеллов

while(@i>0)
begin
	if ascii(RIGHT(@str,1)) between ascii('0') and ascii('9')
	set @str_out=RIGHT(@str,1)+@str_out
	
	set @str=LEFT(@str, LEN(@str)-1)
	set @i=@i-1
end

return @str_out
end

--select dbo.f_only_digits_in_jazz('Phone: 8 (912)29-55 55-5 ')
1 сен 19, 16:20    [21961483]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Сруль.
Member

Откуда:
Сообщений: 112
Слышали сказку про последний баг ?
Ну так послушайте.
Если строка заканчивается на пробеллы, то функция хоть и не вылетает, но считает неправильно.
Поэтому вот ещё одна версия. Думаю, что дальше разберётесь без меня.
А себе, я наработачку оставил, можа пригодится.
Спасибо автору темы за идею.
alter function dbo.f_only_digits_in_jazz(@str nvarchar(1000))
returns nvarchar(1000)
as
begin

declare @i int,@str_out nvarchar(1000)

select @i=LEN(@str),@str_out=''
,@str=REPLACE(rtrim(@str),' ','~')--против пробеллов

while(@i>0)
begin
	if ascii(RIGHT(@str,1)) between ascii('0') and ascii('9')
	set @str_out=RIGHT(@str,1)+@str_out
	
	set @str=LEFT(@str, LEN(@str)-1)
	set @i=@i-1
end

return @str_out
end

--select dbo.f_only_digits_in_jazz('  7 6 98 345 Phone: 8 (912)29-55 55-5   ')
1 сен 19, 16:37    [21961491]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1041
Сруль.
Слышали сказку про последний баг ?
Ну так послушайте.
Если строка заканчивается на пробеллы, то функция хоть и не вылетает, но считает неправильно.
Поэтому вот ещё одна версия. Думаю, что дальше разберётесь без меня.
А себе, я наработачку оставил, можа пригодится.
Спасибо автору темы за идею.

Ну, убожество то может быть и убожество, но промежуточный результат выдает (на тех же данных):

 SQL Server Execution Times:
CPU time = 1156 ms, elapsed time = 1421 ms.
2 сен 19, 09:53    [21961651]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6697
Теперь давайте мерятся производительностью.
2 сен 19, 12:04    [21961753]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 926
Minamoto,
на моей машине ваш вариант проиграл
Invalid object name 'dbo.number'.
3 сен 19, 18:09    [21962782]     Ответить | Цитировать Сообщить модератору
 Re: Как исключить все символы из NVARCHAR оставив только числа от 0..9  [new]
Yasha123
Member

Откуда:
Сообщений: 1440
Руслан Дамирович
Minamoto,
на моей машине ваш вариант проиграл
Invalid object name 'dbo.number'.

ну так таблицу чисел каждый сам себе создает
и называет, как хочет.
у меня это dbo.Nums, например.
3 сен 19, 18:17    [21962788]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить