Format a Phone Number in SQL Server (T-SQL)
Here are some examples of formatting phone numbers in SQL Server.
This includes examples of formatting numbers in E.164 format (for international numbers), prepending the country code and area code, as well as omitting leading zero from the country code when required.
Numeric Phone Numbers
If the phone number is stored as a numeric value (which it shouldn’t be), you can use the FORMAT() function to format it as phone number.
The first argument is the phone number and the second argument is the format string. In this example I’m using a custom format string. You can adjust the format string to suit the desired phone number format:
It’s important to know what the format strings actually do. When using zeros, you’ll need to make sure that the phone number actually has digits in every place where there’s a zero format specifier (otherwise you might accidentally add zeros to the number).
You’ll also need to ensure that there’s a format specifier to match each digit (otherwise you’ll delete digits from the phone number).
Another way to express the format string is with the # format specifier. However, this will result in any leading zeros being removed from the phone number.
Here’s an example to illustrate what I mean:
The FORMAT() function only accepts numeric types and datetime values. If the input phone number isn’t actually a numeric type, then you’ll probably get an error, something like this:
In such cases, it’s easy enough to convert the value to a numeric type:
But phone numbers shouldn’t be stored as numeric types anyway.
Numeric values can be rounded up or down, have calculations performed on them, have insignificant zeros automatically removed, etc.
Phone numbers are a fixed value. Every digit is significant (including leading zeros). We don’t want leading zeros to go missing unless we explicitly require this (for a country code for example). And we don’t want our phone numbers to be inadvertently rounded up or down. And it’s unlikely you’ll ever need to perform calculations on your phone numbers.
So it therefore makes more sense to store phone numbers as a string. Converting them to a numeric type before formatting them (like in the above example) could still result in unexpected changes being done to the number.
If the phone number is already a string, then try the following method.
Phone Numbers Stored as Strings
If the phone number is stored as a string, you can use the STUFF() function to insert the appropriate strings into the phone number at the relevant places.
The first argument is the original string (in this case, the phone number), and the fourth argument is the string to insert. The second argument specifies where to insert the fourth argument.
The third argument specifies how many characters to delete from the original string (in case you want to replace certain characters with the new string). In our case, we don’t want to delete any characters, and so we use 0 .
Depending on the format of the original phone number, another way to do it is to use the REPLACE() function. An example of where this could be useful is when the phone number is already formatted with a separator, but it needs to be replaced with another separator:
International Numbers
E.164 is an international standard that defines the format for international telephone numbers.
E.164 numbers are formatted [+][country code][area code][local phone number] and can have a maximum of fifteen digits.
Here’s an example that uses two methods to concatenate the country code, the area code, and the phone number:
The first method uses the CONCAT() function, and the second uses the concatenation operator ( + ).
That example formats a US based number. In many countries, the area code has a leading zero that needs to be dropped when using the E.164 format.
One way to suppress any leading zeros is to convert the area code to a numeric value and back again.
Here’s an example of using that technique on a UK based number:
In this case the leading zero was dropped.
Here’s the same code run against the previous US based number (which doesn’t use a leading zero in the country code):
Какой тип данных следует использовать для хранения телефонных номеров в SQL Server 2005?
Это поле должно быть сильно индексировано, поскольку торговые представители могут использовать это поле для поиска (включая поиск диких символов).
на данный момент мы ожидаем, что телефонные номера будут представлены в нескольких форматах (из XML-файла). Нужно ли писать парсер для преобразования в единый формат? Там могут быть миллионы данных (с дубликатами) и я не хочу связывать ресурсы сервера (в таких действиях, как предварительная обработка слишком много) каждый раз, когда некоторые исходные данные поступают..
любые предложения приветствуются..
обновление: У меня нет контроля над источником данных. Просто структура xml-файла является стандартной. Хотелось бы свести синтаксический анализ xml к минимуму.
После того, как он находится в базе данных, извлечение должно быть быстрым. Одно сумасшедшее предложение, которое происходит здесь, заключается в том, что он должен даже работать с Ajax Функция автозаполнения (так что торговые представители могут видеть соответствующие из них сразу). ОМГ!!
Какой тип данных следует использовать для хранения телефонных номеров в SQL Server 2005?
Это поле необходимо тщательно проиндексировать, поскольку торговые представители могут использовать это поле для поиска (включая поиск с использованием диких символов).
На данный момент мы ожидаем, что номера телефонов будут представлены в нескольких форматах (из файла XML). Надо ли писать парсер для преобразования в единый формат? Могут быть миллионы данных (с дубликатами), и я не хочу связывать ресурсы сервера (например, слишком много предварительной обработки) каждый раз, когда приходят какие-то исходные данные.
Любые предложения приветствуются.
Обновление: Я не контролирую исходные данные. Просто структура xml файла стандартная. Хотелось бы свести синтаксический анализ xml к минимуму. Как только он будет в базе данных, поиск должен быть быстрым. Есть одно безумное предложение: он должен работать даже с функцией Ajax AutoComplete (чтобы торговые представители могли сразу увидеть подходящие). МОЙ БОГ!!
Форум пользователей MySQL
Некогда давно создавалась база для хранения некой информации, в том числе и номеров телефонов.
Формат хранение следующий:
1. Таблица стран с полями id, name, phone_code
2. Таблица данных, где телефоны хранятся в следующем виде country_id(int), phone_code(int), phone_number(int).
Таблица данных порядка
200000 записей, и используется активно, в том числе добавление и удаление записей.
В данный момент столкнулись с проблемой, что номера телефонов начинаются с нуля, и при добавлении в базу эти нули "съедаются", в результате записывается и отображается при выводе неправильный номер.
Как вариант, для лечения данной проблемы, можно поле для кода и номера изменить на char или varchar, но есть проблема.
При добавлении записи в таблицу идёт проверка сколько записей с данным телефонным номером существует, и если поля будут типа char или varchar, то такие запросы оказываются тяжелее по сравнению с полями типа int.
Подскажите, как можно решить проблему "съедания" нулей, без проседания производительности при поиске ?
#2 21.10.2013 20:45:30
Re: Поля для хранение телефонных номеров в mysql
Если длинна номера телефона фиксирована, то проще при отображении дописывать нужное кол-во нулей.
MariaDB [ test ] > create table test ( id int ( 7 ) unsigned zerofill ) ;
Query OK, 0 rows affected ( 0.19 sec )
MariaDB [ test ] > insert into test values ( 433 ) ;
Query OK, 1 row affected ( 0.06 sec )
#3 22.10.2013 11:59:39
Re: Поля для хранение телефонных номеров в mysql
Вариант с zerofill не подойдёт, длина номера не фиксированная.
#4 22.10.2013 15:20:15
Re: Поля для хранение телефонных номеров в mysql
Тогда или varchar (но это большие расходы на индекс), или втрое поле,в котором хранить длину номера, а при выводе отображать нужное кол-во нулей.
#5 23.10.2013 18:25:38
Re: Поля для хранение телефонных номеров в mysql
vasya написал:
Тогда или varchar (но это большие расходы на индекс), или втрое поле,в котором хранить длину номера, а при выводе отображать нужное кол-во нулей.
добавлять новое поле тоже не очень приемлимый вариант, потому что нужно будет дорабатывать софт под такое поле, ну и ещё нужно тестировать, не будет ли это более ресурсоемко добавление ещё одного поля в where, чем перевести поля код и номер в varchar. Разве что, если только найти в mysql или самому написать функцию, которая в однозначном порядке будет конвертировать весь телефонный номер в число и использовать это поле для поиска, что-то вроде функций ATON и NTOA для ip-адресов.
С индексом этих полей тоже немного непонятно, проводил тестирование на таблице в которой больше миллиона записей, таблица с разными данными и в ней поля как писал выше country_id, phone_code, phone_number.
Поля phone_code и phone_number делаю varchar, пробую делать индекс из трёх полей country_id, phone_code и phone_number, и три отдельных индекса по каждому полю, и делаю запрос:
SELECT COUNT(*) FROM table WHERE country_id = ‘x’ && concat(phone_code,phone_number) = ‘zzzyyyyyyy’
В таком запросе mysql отказывается использовать данные индексы. Может есть ещё вариант как сделать индексы чтоб поиск шёл по индексам, а не по всей таблице?
И ещё небольшой вопрос, если под поля phone_code и phone_number предусмотреть размер не более 6 и 8 символов соответственно, теоритически, должен ли быть выйгрыш при поиске если сделать данные поля char(6) и char(8) вместо varchar(6) и varchar(8) ?
На своей тестовой базе пробовал так и так, разницы не заметно.
#6 23.10.2013 19:19:08
Re: Поля для хранение телефонных номеров в mysql
_Igor_ написал:
С индексом этих полей тоже немного непонятно, проводил тестирование на таблице в которой больше миллиона записей, таблица с разными данными и в ней поля как писал выше country_id, phone_code, phone_number.
Поля phone_code и phone_number делаю varchar, пробую делать индекс из трёх полей country_id, phone_code и phone_number, и три отдельных индекса по каждому полю, и делаю запрос:
SELECT COUNT(*) FROM table WHERE country_id = ‘x’ && concat(phone_code,phone_number) = ‘zzzyyyyyyy’
В таком запросе mysql отказывается использовать данные индексы. Может есть ещё вариант как сделать индексы чтоб поиск шёл по индексам, а не по всей таблице?
Bндекс не будет использоваться, если в части where поле входит в состав какого-нибудь выражения.
В вашем случае нужно убрать concat:
SELECT COUNT(*) FROM table WHERE country_id = ‘x’ && phone_code = ‘zzz’ && phone_number = ‘yyyyyyy’; — тогда составной индекс будет использоваться.
_Igor_ написал:
И ещё небольшой вопрос, если под поля phone_code и phone_number предусмотреть размер не более 6 и 8 символов соответственно, теоритически, должен ли быть выйгрыш при поиске если сделать данные поля char(6) и char(8) вместо varchar(6) и varchar(8) ?