Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Поиск номера телефона, если он в поле nvarchar, да ещё может быть и не один  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 508
Здравствуйте!

Есть таблица, в которой в поле nvarchar(50), хранятся номера телефонов. Хранятся в хаотическом состоянии, например:
ххх-хх-хх
х-ххх-ххх
ххххххх
(ххх) хххх-ххх

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

есть ли способ получить все записи, в которые входит часть искомых цифр?
или для этого потребуется делать некий скрипт, который отделял бы вначале числа, а затем уже в этих цифрах производил поиск?

Сервер: Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
9 сен 13, 00:30    [14811629]     Ответить | Цитировать Сообщить модератору
 Re: Поиск номера телефона, если он в поле nvarchar, да ещё может быть и не один  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5499
Блог
Для начала - нужно определиться с алгоритмом - какая строка считается телефоном, а какая - нет.
Дальше - проще.
9 сен 13, 00:33    [14811633]     Ответить | Цитировать Сообщить модератору
 Re: Поиск номера телефона, если он в поле nvarchar, да ещё может быть и не один  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 508
DeColo®es
Для начала - нужно определиться с алгоритмом - какая строка считается телефоном, а какая - нет.
Дальше - проще.


во всех строках есть телефон в каком либо виде.
номер телефона не может быть менее 7-ми символов.
номер может включать в себя код города и код страны, эти коды также являются частью номера телефона.

есть один трудный нюанс, кто-то мог оставить сразу несколько номеров, например разных операторов... тут не знаю как быть. но в крайнем случае хотя бы выделять из строки первый телефон. например, замечены такие конструкции:
(911) 5673232; (064)1231231
+710123123123123 777777777
Билайн 3-453-451 мтс 567-56-71
9 сен 13, 00:43    [14811654]     Ответить | Цитировать Сообщить модератору
 Re: Поиск номера телефона, если он в поле nvarchar, да ещё может быть и не один  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
palladin600
или для этого потребуется делать некий скрипт, который отделял бы вначале числа, а затем уже в этих цифрах производил поиск?
Ага

Так что лучше хранить в нормализованной форме...
9 сен 13, 00:58    [14811682]     Ответить | Цитировать Сообщить модератору
 Re: Поиск номера телефона, если он в поле nvarchar, да ещё может быть и не один  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4262
palladin600, для одной из своих систем я написал простенькую функцию.
CREATE FUNCTION [dbo].[NormalizePhoneNumber]
(
	@phone nvarchar(150)
)
RETURNS nvarchar(16)
AS
BEGIN
	declare @pos int
	declare @len int

	set @pos = CHARINDEX(N',',@phone,1);

	set @len = ISNULL(nullif(@pos-1,-1),len(@phone));
	set @phone = substring(@phone,1,@len);
	set @phone = REPLACE(@phone,'+7','8');

	set @phone = REPLACE(@phone,' ','');
	set @phone = REPLACE(@phone,'-','');
	set @phone = REPLACE(@phone,'+','');
	set @phone = REPLACE(@phone,'_','');

	set @len = LEN(@phone);
	if @len = 11
	begin
		set @phone = SUBSTRING(@phone,1,1) + '(' + SUBSTRING(@phone,2,3) + ')' 
			+ SUBSTRING(@phone,5,3) + '-' +
			+ SUBSTRING(@phone,8,2) + '-' +
			+ SUBSTRING(@phone,10,2);		
	end
	if @len = 7
	begin
		set @phone = SUBSTRING(@phone,1,3) + '-' +
			+ SUBSTRING(@phone,4,2) + '-' +
			+ SUBSTRING(@phone,6,2);		
	end

	return @phone   

END

Конечно она не идеальная по качеству кода (реплейсы можно было объеденить), но для моих потребностей её достаточно.
Можно взять её за основу и допилить под себя.
9 сен 13, 02:08    [14811804]     Ответить | Цитировать Сообщить модератору
 Re: Поиск номера телефона, если он в поле nvarchar, да ещё может быть и не один  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4262
Собственно функция только приводит номер телефона написанный абы как к виду
8(999)111-22-33 или 111-22-33 если не указан код оператора.
Несколько номеров телефонов в одной строке не обрабатываются.
9 сен 13, 02:23    [14811813]     Ответить | Цитировать Сообщить модератору
 Re: Поиск номера телефона, если он в поле nvarchar, да ещё может быть и не один  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 508
получается... вначале надо всё нормализовать.
т.е. также никакого регекса не получится использовать, мол, скомпоновать только цифры и искать только в них, игнорируя все non-numeric символы, даже если в поле зашифрованы 2 номера, то пусть всё равно складываются цифры в одно число. Ничего страшного, если искомые цифры могут быть концом одного телефона и началом следующего. Ищем в сконкатенированных полях без non-numeric, а выдаём реальное содержимое поля.

Ежели такого способа нет, то блин чувствую придётся нормализовать вручную всё. Ведь придётся всем телефонам прибавлять код оператора в зависимости от первой цифры в номере телефона...
9 сен 13, 02:40    [14811819]     Ответить | Цитировать Сообщить модератору
 Re: Поиск номера телефона, если он в поле nvarchar, да ещё может быть и не один  [new]
Merdoc
Member

Откуда: Новосибирск
Сообщений: 103
Убрать пробел после закрывающий скобки чтобы не отрезать код города и попробовать разделить на подстроки по наличию пробела или ',' ';'. После чего порезать все не числовые символы.
Хотя конечно без проверки тут все равно не обойтись, но в принципе возможно.

У мя вопрос по сути. Для задачи
есть ли способ получить все записи, в которые входит часть искомых цифр?
можно же использоваться обычный like, совсем не обязательно приводить в нормальному виду?
Ну будете искать как то так like '%1%2%3%4%5%' , выборка в итоге конечно больше, но найти реально, если искать не по двум цифрам.
9 сен 13, 07:37    [14811881]     Ответить | Цитировать Сообщить модератору
 Re: Поиск номера телефона, если он в поле nvarchar, да ещё может быть и не один  [new]
Exproment
Member

Откуда:
Сообщений: 416
если найти такие телефоны разовая операция, то можно не запариваться и пройтись UDF функцией. Если же нужен оптимизированный механизм, то необходимо для начала привести к первой нормальной форме(несколько значений не могут быть в одном поле).
Затем необходимо стандартизировать данные. Тут уж вам все карты в руки. Я вижу два метода:
1) Ограничениями задать возможные структуры номеров что-то типа "like... or like... or like..." => тогда таким-же количеством лайков сможем найти нужные номера без UDF
2) Создать дополнительную табличку мол (тип номера)(ограничение)
2.1) в столбце сохраняем только цифры(в виде varchar) при этом триггерами проверяем совместимость с ограничениями
2.2) для клиента вид номера формируем исходя из его типа(можно через view)
2.3) вот теперь в большинстве случаев сможем организовать быстрый поиск по времени

Поиск посредством like '%1%' крайне дорогостоющая операция. Необходимо хотябы начало строки знать. В текущей архитектуре вообще ничего быстрого нельзя делать)
9 сен 13, 09:16    [14812027]     Ответить | Цитировать Сообщить модератору
 Re: Поиск номера телефона, если он в поле nvarchar, да ещё может быть и не один  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
palladin600
т.е. также никакого регекса не получится использоват
Можно написать свою функцию, не обязательно нормализовывать вручную.

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

В нормализованном виде нужно:
- во первых, хранить телефоны по одному, со связкой один-ко-многим (то есть не хранить по несколько номеров в одном поле),
- во вторых, хранить номер в определённом формате, лучше всего в общепринятом, например, последовательность цифр начиная с кода страны: 79161234567, а в нужный формат преобразовывать при выводе, как это делается при работе со всеми типами.
9 сен 13, 10:25    [14812377]     Ответить | Цитировать Сообщить модератору
 Re: Поиск номера телефона, если он в поле nvarchar, да ещё может быть и не один  [new]
Sp999
Member

Откуда: Пермь
Сообщений: 1669
Я для подобной задачи юзал функцию patindex:
where patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', t.Phone) > 0
or patindex('%([0-9][0-9][0-9])[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%', t.Phone) > 0
...
9 сен 13, 13:45    [14813818]     Ответить | Цитировать Сообщить модератору
 Re: Поиск номера телефона, если он в поле nvarchar, да ещё может быть и не один  [new]
Денис Б.
Member

Откуда: Екатеринбург
Сообщений: 158
можно составить алгоритм выделения только цифровых значений

REPLACE
_( - _
)_ - ""
) - ""
- - ""
_+-_
+ - ""
, - _
; - _
__ - _
получаем чистые номера с пробелом в качестве разделителя.
10 сен 13, 09:00    [14817703]     Ответить | Цитировать Сообщить модератору
 Re: Поиск номера телефона, если он в поле nvarchar, да ещё может быть и не один  [new]
o-o
Guest
Денис Б.
можно составить алгоритм выделения только цифровых значений

REPLACE
_( - _
)_ - ""
) - ""
- - ""
_+-_
+ - ""
, - _
; - _
__ - _
получаем чистые номера с пробелом в качестве разделителя.


ой-ли?
Билайн 3-453-451 мтс 567-56-71 -> Билайн 3453451 мтс 5675671
10 сен 13, 11:48    [14818777]     Ответить | Цитировать Сообщить модератору
 Re: Поиск номера телефона, если он в поле nvarchar, да ещё может быть и не один  [new]
Денис Б.
Member

Откуда: Екатеринбург
Сообщений: 158
o-o
ой-ли?
Билайн 3-453-451 мтс 567-56-71 -> Билайн 3453451 мтс 5675671

если разделитель - слово, то тогда действительно проблема. нужна другая эвристика.
билайн и мтс - легко, а вот TELE2 (или ЕКАТЕРИНБУРГ-2000(он же МОТИВ) или ещё что со словами: любовница№11, офис 666, Ленина 1)-тут проблема.
10 сен 13, 13:39    [14819748]     Ответить | Цитировать Сообщить модератору
 Re: Поиск номера телефона, если он в поле nvarchar, да ещё может быть и не один  [new]
o-o
Guest
Денис Б.,
это был намек, что проще выцеплять цыфры, благо их всего 10,
чем реплейсить целые алфавиты
(см. примеры данных из поста N3)
10 сен 13, 13:46    [14819802]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить