forked from rin-nas/postgresql-patterns-library
-
Notifications
You must be signed in to change notification settings - Fork 1
/
phone_normalize.sql
186 lines (165 loc) · 8.21 KB
/
phone_normalize.sql
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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
-- TODO add support for https://datatracker.ietf.org/doc/html/rfc3966 (see https://habr.com/ru/post/278345/)
create or replace function phone_normalize(
country_code int, --код страны в любом формате или NULL
area_code text, --код зоны в любом формате или NULL
local_number text --локальный номер телефона в любом формате или NULL
)
returns text
immutable
--returns null on null input
parallel safe
language plpgsql
cost 10
as
$$
declare
phone text;
is_starts_with_plus boolean;
begin
-- not valid speed improves
if country_code not between 0 and 999
or coalesce(length(area_code), 0) + coalesce(length(local_number), 0) not between 5 and 100
or concat_ws('', area_code, local_number) !~ '\d\d\D*\d\d'
then
return null;
end if;
is_starts_with_plus := country_code is null and concat_ws('', area_code, local_number) ~ '^\D*?\+';
area_code := trim(regexp_replace(area_code, '(?:^\D+|\D+$|[ ()\-.]+|&(?:ndash|minus);)', ' ', 'g'));
local_number := trim(regexp_replace(local_number, '(?:^\D+|\D+$|[ ()\-.]+|&(?:ndash|minus);)', ' ', 'g'));
phone := replace(concat_ws('', area_code, local_number), ' ', '');
phone := regexp_replace(phone, '(?<![а-яё])[сc]\d+[дп]о\d+ч?', '');
phone := regexp_replace(phone, '(?<=\d)(доб|вн|ext)\d+$', ''); --добавочный номер
phone := regexp_replace(phone, '(?<=\d)(/\d\d)+$', ''); --альтернативные номера через слэш
phone := replace(phone, '/', '');
--raise notice 'stage 1: %', phone;
if phone !~ '^\d{5,15}$'
then
return null;
end if;
if country_code in (7, 8) and phone ~ '^[78]\d{10}$'
then
country_code := null; --deduplication
end if;
phone := concat_ws(' ', country_code, area_code, local_number);
phone := regexp_replace(phone, '^[78] [78] ', '7'); --deduplication
phone := replace(phone, ' ', '');
phone := regexp_replace(phone, '(?<![а-яё])[сc]\d+[дп]о\d+ч?', '');
phone := regexp_replace(phone, '(?<=\d)(доб|вн|ext)\d+$', ''); --добавочный номер
phone := regexp_replace(phone, '(?<=\d)(/\d\d)+$', ''); --альтернативные номера через слэш
phone := replace(phone, '/', '');
--raise notice 'stage 2: %', phone;
/* --DEPRECATED
if phone ~ '(\d)\1{8}' --все цифры одинаковые
then
return null;
end if;
*/
if country_code is null and octet_length(phone) = 10 and not is_starts_with_plus
then
phone = '7' || phone;
end if;
if octet_length(phone) not
between 8 --https://stackoverflow.com/questions/14894899/what-is-the-minimum-length-of-a-valid-international-phone-number
and 15 --https://en.wikipedia.org/wiki/E.164 and https://en.wikipedia.org/wiki/Telephone_numbering_plan)
then
return null;
end if;
return '+' || regexp_replace(phone, '^8', '7');
end;
$$;
comment on function phone_normalize(
country_code int,
area_code text,
local_number text
) is $$
Принимает на входе номер телефона в 3-х полях, каждое из которых может быть null!
Возвращает номер телефона в международном формате E.164, например: +79651234567
При необходимости, пытается исправить ошибки:
* заменяет национальный префикс 8 на код страны 7
* дополняет номер телефона кодом страны 7, если его нехватает
* удаляет дубликаты национального префикса или кода страны
* удаляет все слова вначале и в конце (например, ФИО), добавочный номер, альтернативный номер и диапазон времени звонка
Возвращает null, если строка не является номером телефона (минимальная проверка синтаксиса).
$$;
create or replace function phone_normalize(
country_code text,
area_code text,
local_number text
)
returns text
immutable
--returns null on null input
parallel safe
language sql
as
$$
select phone_normalize(
nullif(trim(country_code), '')::int,
area_code,
local_number
)
$$;
create or replace function phone_normalize(
phone text
)
returns text
immutable
returns null on null input
parallel safe
language sql
as
$$
select
case when --speed improves: номер телефона в международном формате E.164 ?
left(phone, 1) = '+'
and octet_length(phone)
between 1/*+*/ + 8 --https://stackoverflow.com/questions/14894899/what-is-the-minimum-length-of-a-valid-international-phone-number
and 1/*+*/ + 15 --https://en.wikipedia.org/wiki/E.164 and https://en.wikipedia.org/wiki/Telephone_numbering_plan)
and phone ~ '^\+\d+$' then phone
else phone_normalize(null, null, phone)
end;
$$;
--TEST
do $$
begin
--positive
assert phone_normalize(' ',null,'+7 (977) 123-45-67') = '+79771234567';
assert phone_normalize(null,null,'+7 (977) 123-45-67') = '+79771234567';
assert phone_normalize(null,null,'+ 7 (977) 123-45-67') = '+79771234567';
assert phone_normalize(null,null,'++ 7 (977) 123-45-67 доб 123 Мария') = '+79771234567';
assert phone_normalize(null,null,'+ 7 (977) 123-45-67 вн. 123') = '+79771234567';
assert phone_normalize(null,null,'+ 7 (977) 123-45-67 моб.') = '+79771234567';
assert phone_normalize(null,null,'8/977/1234567') = '+79771234567';
assert phone_normalize(null,null,'8 (812) 123–45−67') = '+78121234567';
assert phone_normalize(null,null,'моб.т. + 7 (977) 123-45-67 ') = '+79771234567';
assert phone_normalize(null,null,' моб. 8 /977/ 123-45-67/69/70 Мария/Иван ') = '+79771234567';
assert phone_normalize(null,null,' моб. 8 (977) 123-45-67/69 Мария Петровна ') = '+79771234567';
assert phone_normalize(null,null,'8(977)123-45-67 с 12.00 до 22.00') = '+79771234567';
assert phone_normalize(null,null,'8(977)123-45-67 с 9 по 11 ч. строго') = '+79771234567';
assert phone_normalize(null,null,'89771234567') = '+79771234567';
assert phone_normalize(null,null,'(977) 123-45-67') = '+79771234567';
assert phone_normalize(null,'8 977','123 45 67') = '+79771234567';
assert phone_normalize(null,'977','123 45 67') = '+79771234567';
assert phone_normalize(null,'831 66 1-23-45',null) = '+78316612345';
assert phone_normalize('7','','8 977 123 45 67') = '+79771234567';
assert phone_normalize(7,'8 977','123 45 67') = '+79771234567';
assert phone_normalize(8,'7 977','123 45 67') = '+79771234567';
assert phone_normalize(7,'977','1234567') = '+79771234567';
assert phone_normalize(8,null,'8 9771234567') = '+79771234567';
assert phone_normalize('8 977 1234567') = '+79771234567';
assert phone_normalize('+79771234567') = '+79771234567';
assert phone_normalize('677 1234567') = '+76771234567';
assert phone_normalize('+677 1234567') = '+6771234567';
assert phone_normalize('210(-.-)7905(-.-)1234567') = '+21079051234567';
--negative
assert phone_normalize(-1,'977','1234567') is null;
assert phone_normalize(1000,'977','1234567') is null;
assert phone_normalize(null,null,null) is null;
assert phone_normalize(null,null,null) is null;
assert phone_normalize(null,' ',' ') is null;
assert phone_normalize(null,null,' 123456 ') is null;
assert phone_normalize(null,' 123456 ',null) is null;
assert phone_normalize(null,null, '1234567890123456') is null;
assert phone_normalize(null,null, '8x977_1234567') is null;
end;
$$;