Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как исключить все символы из NVARCHAR оставив только числа от 0..9 / 22 сообщений из 22, страница 1 из 1
30.08.2019, 14:58
    #39855696
Игорь_UUS
Игорь_UUS
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
Добрый день.

Подскажите самый правильны способ, использую MSSQL2016
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.08.2019, 15:41
    #39855726
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
Игорь_UUS,

Вы решили показывать фокусы при помощи SQL? Это вообще не является задачей SQL server.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.08.2019, 16:51
    #39855764
Игорь_UUS
Игорь_UUS
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
Владислав Колосов,

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

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

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

в итоге должен получить только 89122955555.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.08.2019, 17:17
    #39855777
Игорь_UUS
Игорь_UUS
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
Владислав Колосов,

вопрос закрыт... спасибо за участие))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.08.2019, 17:19
    #39855779
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
Игорь_UUS,

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

Код: c#
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
// Автор: Колосов В.В.
// Дата создания: 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));

    }

};



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

Код: sql
1.
select dbo.RegexReplace('Phone:8(912)29-5555-5','\D+','')
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.08.2019, 17:21
    #39855781
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
Хм, комментарий неправильный.
Надо читать "заменяет значение".
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.08.2019, 17:49
    #39855797
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
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);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.08.2019, 17:52
    #39855800
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
Недавно же была такая тема!
Вот, опять ссылку дам: выборка чисел из строки

А в той теме invm давал решение через XML, если я правильно помню.
Но я по своей тупости в XML слабоват...
Поищите сами. Совсем недавно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.08.2019, 17:53
    #39855801
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
iapНедавно же была такая тема!
Вот, опять ссылку дам: выборка чисел из строки

А в той теме invm давал решение через XML, если я правильно помню.
Но я по своей тупости в XML слабоват...

Поищите сами. Совсем недавно.О! А вот и оно! Решение от invm!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.08.2019, 17:54
    #39855802
Minamoto
Minamoto
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
Игорь_UUSВладислав Колосов,

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

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

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.08.2019, 18:01
    #39855805
Minamoto
Minamoto
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
MinamotoИгорь_UUSВладислав Колосов,

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

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

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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:

Код: plaintext
1.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 5 ms.

Мой вариант:

Код: plaintext
1.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.08.2019, 18:06
    #39855808
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
MinamotoMinamotoпропущено...
Ээ, так неинтересно, куда это вы?

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

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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:

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

Код: plaintext
1.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.



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

Прогоните оба варианта на бОльших объемах (пару сотен тысяч строк), это уже можно будет сравнивать
Ну и желательно поиграться с размером строк и их заполненностью цифрами.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.08.2019, 18:15
    #39855811
Minamoto
Minamoto
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
msLexMinamotoпропущено...

Вариант invm:

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

Код: plaintext
1.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.



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

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

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

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

Вариант invm:
Код: plaintext
1.
Msg 9416, Level 16, State 1, Line 40
XML parsing: line 1, character 10, hexadecimal digit expected
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.08.2019, 18:29
    #39855816
Minamoto
Minamoto
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
MinamotoВариант invm:
Код: plaintext
1.
Msg 9416, Level 16, State 1, Line 40
XML parsing: line 1, character 10, hexadecimal digit expected

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

Код: plaintext
1.
 SQL Server Execution Times:
   CPU time = 3969 ms,  elapsed time = 3976 ms.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.09.2019, 14:40
    #39856089
Сруль.
Сруль.
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
Может, чего не понял, функция-то, несложная.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
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')
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.09.2019, 16:00
    #39856095
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
[quot Сруль.]Может, чего не понял, функция-то, несложная.
Это императивное убожество.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.09.2019, 16:20
    #39856097
Сруль.
Сруль.
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
Насчёт убожеств, это не ко мне, я больше на результат.
Здесь есть конкретный баг.
Когда в строке замешан пробел, функция просто вылетает, т.к. длина считается неправильно.
Поэтому несколько исправленная версия.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
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 ')
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.09.2019, 16:37
    #39856100
Сруль.
Сруль.
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
Слышали сказку про последний баг ?
Ну так послушайте.
Если строка заканчивается на пробеллы, то функция хоть и не вылетает, но считает неправильно.
Поэтому вот ещё одна версия. Думаю, что дальше разберётесь без меня.
А себе, я наработачку оставил, можа пригодится.
Спасибо автору темы за идею.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
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   ')
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.09.2019, 09:53
    #39856186
Minamoto
Minamoto
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
Сруль.Слышали сказку про последний баг ?
Ну так послушайте.
Если строка заканчивается на пробеллы, то функция хоть и не вылетает, но считает неправильно.
Поэтому вот ещё одна версия. Думаю, что дальше разберётесь без меня.
А себе, я наработачку оставил, можа пригодится.
Спасибо автору темы за идею.

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

Код: plaintext
1.
 SQL Server Execution Times:
   CPU time = 1156 ms,  elapsed time = 1421 ms.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.09.2019, 12:04
    #39856231
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
Теперь давайте мерятся производительностью.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.09.2019, 18:09
    #39856814
Руслан Дамирович
Руслан Дамирович
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
Minamoto,
на моей машине ваш вариант проиграл
Код: sql
1.
Invalid object name 'dbo.number'.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.09.2019, 18:17
    #39856819
Yasha123
Yasha123
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как исключить все символы из NVARCHAR оставив только числа от 0..9
Руслан ДамировичMinamoto,
на моей машине ваш вариант проиграл
Код: sql
1.
Invalid object name 'dbo.number'.


ну так таблицу чисел каждый сам себе создает
и называет, как хочет.
у меня это dbo.Nums, например.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как исключить все символы из NVARCHAR оставив только числа от 0..9 / 22 сообщений из 22, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]