У меня есть таблица в базе данных с колонкой номера телефона. Цифры выглядят так:
123456789
Я хочу отформатировать это так:
123-456-789
Как форматировать числовой столбец в качестве номера телефона в SQL
Ответ 1
Это должно сделать это:
UPDATE TheTable
SET PhoneNumber = SUBSTRING(PhoneNumber, 1, 3) + '-' +
SUBSTRING(PhoneNumber, 4, 3) + '-' +
SUBSTRING(PhoneNumber, 7, 4)
Incorporated Kane suggestion, вы можете вычислить форматирование номера телефона во время выполнения. Одним из возможных подходов было бы использование скалярных функций для этой цели (работает в SQL Server):
CREATE FUNCTION FormatPhoneNumber(@phoneNumber VARCHAR(10))
RETURNS VARCHAR(12)
BEGIN
RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' +
SUBSTRING(@phoneNumber, 4, 3) + '-' +
SUBSTRING(@phoneNumber, 7, 4)
END
Ответ 2
Обычно я рекомендую вам оставить форматирование до вашего кода переднего плана и просто вернуть данные как есть из SQL. Однако, чтобы сделать это в SQL, я бы рекомендовал вам создать пользовательскую функцию для ее форматирования. Что-то вроде этого:
CREATE FUNCTION [dbo].[fnFormatPhoneNumber](@PhoneNo VARCHAR(20))
RETURNS VARCHAR(25)
AS
BEGIN
DECLARE @Formatted VARCHAR(25)
IF (LEN(@PhoneNo) <> 10)
SET @Formatted = @PhoneNo
ELSE
SET @Formatted = LEFT(@PhoneNo, 3) + '-' + SUBSTRING(@PhoneNo, 4, 3) + '-' + SUBSTRING(@PhoneNo, 7, 4)
RETURN @Formatted
END
GO
Что вы можете использовать следующим образом:
SELECT [dbo].[fnFormatPhoneNumber](PhoneNumber) AS PhoneNumber
FROM SomeTable
У этого есть гарантия, в случае, если номер телефона не является ожидаемым числом цифр, пустым, нулем и т.д. - это не будет ошибкой.
РЕДАКТИРОВАТЬ: Просто синхронизированный, вы хотите обновить существующие данные. Основной бит, относящийся к моему ответу, заключается в том, что вам нужно защитить от "изворотливых" /неполных данных (то есть, если некоторые существующие значения имеют длину всего 5 символов)
Ответ 3
Я не рекомендую хранить плохие данные в базе данных, а затем исправлять их только на выходе. У нас есть база данных, где номера телефонов вводятся по-разному:
- (555) 555-5555
- 555 + 555 + 5555
- 555.555.5555
- (555) 555-5555
- 5555555555
Различные люди в организации могут писать различные функции поиска и обновления базы данных, поэтому было бы сложнее установить правила форматирования и извлечения. Поэтому я исправляю данные в базе данных в первую очередь, а затем устанавливаю правила и формы, которые защищают целостность этой базы данных в будущем.
Я не вижу оправдания для сохранения плохих данных, если только не предлагается добавить дублирующийся столбец с исправленным форматированием и исходными данными, содержащимися для избыточности и ссылки, и ДА Я рассматриваю плотно отформатированные данные как данные BAD.
Ответ 4
Как упоминалось выше, эти решения очень просты, и они не будут работать, если в базе данных есть разные форматы телефона, такие как: (123)123-4564 123-456-4564 1234567989 и т.д.
Вот более сложное решение, которое будет работать с ЛЮБОЙ ввод данных:
CREATE FUNCTION [dbo].[ufn_FormatPhone]
(@PhoneNumber VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @Phone CHAR(32)
SET @Phone = @PhoneNumber
-- cleanse phone number string
WHILE PATINDEX('%[^0-9]%',@PhoneNumber) > 0
SET @PhoneNumber = REPLACE(@PhoneNumber,
SUBSTRING(@PhoneNumber,PATINDEX('%[^0-9]%',@PhoneNumber),1),'')
-- skip foreign phones
IF (SUBSTRING(@PhoneNumber,1,1) = '1'
OR SUBSTRING(@PhoneNumber,1,1) = '+'
OR SUBSTRING(@PhoneNumber,1,1) = '0')
AND LEN(@PhoneNumber) > 11
RETURN @Phone
-- build US standard phone number
SET @Phone = @PhoneNumber
SET @PhoneNumber = '(' + SUBSTRING(@PhoneNumber,1,3) + ') ' +
SUBSTRING(@PhoneNumber,4,3) + '-' + SUBSTRING(@PhoneNumber,7,4)
IF LEN(@Phone) - 10 > 1
SET @PhoneNumber = @PhoneNumber + ' X' + SUBSTRING(@Phone,11,LEN(@Phone) - 10)
RETURN @PhoneNumber
END
Ответ 5
Решения, использующие SUBSTRING и конкатенацию +, практически не зависят от СУБД. Вот короткое решение, специфичное для SQL Server:
declare @x int = 123456789
select stuff(stuff(@x, 4, 0, '-'), 8, 0, '-')
Ответ 6
Вы также можете попробовать следующее:
CREATE function [dbo].[fn_FormatPhone](@Phone varchar(30))
returns varchar(30)
As
Begin
declare @FormattedPhone varchar(30)
set @Phone = replace(@Phone, '.', '-') --alot of entries use periods instead of dashes
set @FormattedPhone =
Case
When isNumeric(@Phone) = 1 Then
case
when len(@Phone) = 10 then '('+substring(@Phone, 1, 3)+')'+ ' ' +substring(@Phone, 4, 3)+ '-' +substring(@Phone, 7, 4)
when len(@Phone) = 7 then substring(@Phone, 1, 3)+ '-' +substring(@Phone, 4, 4)
else @Phone
end
When @phone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' Then '('+substring(@Phone, 1, 3)+')'+ ' ' +substring(@Phone, 5, 3)+ '-' +substring(@Phone, 8, 4)
When @phone like '[0-9][0-9][0-9] [0-9][0-9][0-9] [0-9][0-9][0-9][0-9]' Then '('+substring(@Phone, 1, 3)+')'+ ' ' +substring(@Phone, 5, 3)+ '-' +substring(@Phone, 9, 4)
When @phone like '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' Then '('+substring(@Phone, 1, 3)+')'+ ' ' +substring(@Phone, 5, 3)+ '-' +substring(@Phone, 9, 4)
Else @Phone
End
return @FormattedPhone
конец
использовать на нем выбрать
(SELECT [dbo].[fn_FormatPhone](f.coffphone)) as 'Phone'
Выход будет
Ответ 7
Я обнаружил, что это работает, если вы хотите в формате (123) - 456-7890.
UPDATE table
SET Phone_number = '(' +
SUBSTRING(Phone_number, 1, 3)
+ ') '
+ '- ' +
SUBSTRING(Phone_number, 4, 3)
+ '-' +
SUBSTRING(Phone_number, 7, 4)
Ответ 8
Обновлена функция @sqiller для моих целей
CREATE FUNCTION [toolbox].[FormatPhoneNumber] (
@PhoneNumber VARCHAR(50),
@DefaultIfUnknown VARCHAR(50)
)
RETURNS VARCHAR(50)
AS
BEGIN
-- remove any extension
IF CHARINDEX('x', @PhoneNumber, 1) > 0
SET @PhoneNumber = SUBSTRING(@PhoneNumber, 1, CHARINDEX('x', @PhoneNumber, 1) - 1)
-- cleanse phone number string
WHILE PATINDEX('%[^0-9]%',@PhoneNumber) > 0
SET @PhoneNumber = REPLACE(@PhoneNumber,
SUBSTRING(@PhoneNumber,PATINDEX('%[^0-9]%',@PhoneNumber),1),'')
-- Remove US international code if exists, i.e. 12345678900
IF SUBSTRING(@PhoneNumber,1,1) = '1' AND LEN(@PhoneNumber) = 11
SET @PhoneNumber = SUBSTRING(@PhoneNumber, 2, 10)
-- any phone numbers without 10 characters are set to default
IF LEN(@PhoneNumber) <> 10
RETURN @DefaultIfUnknown
-- build US standard phone number
SET @PhoneNumber = '(' + SUBSTRING(@PhoneNumber,1,3) + ') ' +
SUBSTRING(@PhoneNumber,4,3) + '-' + SUBSTRING(@PhoneNumber,7,4)
RETURN @PhoneNumber
END
Ответ 9
Если вы хотите просто форматировать вывод, вам не нужно создавать новую таблицу или функцию. В этом случае код области находился в отдельных полях. Я использую field1, field2 только для иллюстрации, вы можете выбрать другие поля в одном запросе:
area phone
213 8962102
Выберите оператор:
Select field1, field2,areacode,phone,SUBSTR(tablename.areacode,1,3) + '-' + SUBSTR(tablename.phone,1,3) + '-' + SUBSTR(tablename.areacode,4,4) as Formatted Phone from tablename
Пример OUTPUT:
columns: FIELD1, FIELD2, AREA, PHONE, FORMATTED PHONE
data: Field1, Field2, 213, 8962102, 213-896-2102
Ответ 10
Вы можете использовать ФОРМАТ, если столбец - это номер Синтаксис, например
ФОРМАТ (значение, формат [, культура]) Используется как
FORMAT ( @d, 'D', 'en-US' ) или FORMAT(123456789,'###-##-####') (Но это работает только для SQL SERVER 2012 и After)
Использовать как
UPDATE TABLE_NAME SET COLUMN_NAME = FORMAT(COLUMN_NAME ,'###-##-####')
И
если ваша колонка используется Varchar или Nvarchar, сделайте так
CONCAT(SUBSTRING(CELLPHONE,0,4),' ',SUBSTRING(CELLPHONE,4,3),' ',SUBSTRING(CELLPHONE,7,2) ,' ',SUBSTRING(CELLPHONE,9,2) )
Вы всегда можете получить справку