Ik heb een tabel in de database met een telefoonnummerkolom. De cijfers zien er als volgt uit:
123456789
Ik wil dat opmaken zodat het er als volgt uitziet:
123-456-789
Antwoord 1, autoriteit 100%
Dit zou het moeten doen:
UPDATE TheTable
SET PhoneNumber = SUBSTRING(PhoneNumber, 1, 3) + '-' +
SUBSTRING(PhoneNumber, 4, 3) + '-' +
SUBSTRING(PhoneNumber, 7, 4)
Met de suggestie van Kane kun je de opmaak van het telefoonnummer tijdens runtime berekenen. Een mogelijke benadering zou zijn om hiervoor scalaire functies te gebruiken (werkt in 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
Antwoord 2, autoriteit 36%
Ik raad u over het algemeen aan om de opmaak over te laten aan uw front-endcode en de gegevens gewoon terug te sturen zoals ze zijn uit SQL. Om het echter in SQL te doen, raad ik u aan een door de gebruiker gedefinieerde functie te maken om het te formatteren. Zoiets als dit:
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
die u vervolgens als dit kunt gebruiken:
SELECT [dbo].[fnFormatPhoneNumber](PhoneNumber) AS PhoneNumber
FROM SomeTable
Het heeft een waarborg in, in het geval dat het opgeslagen telefoonnummer niet het verwachte aantal cijfers lang is, is leeg, null enz. – Het is niet fout.
bewerken: zojuist geklokt op u wilt uw bestaande gegevens bijwerken. Het belangrijkste bit dat relevant is uit mijn antwoord, is dat u moet beschermen tegen “dodgy” / onvolledige gegevens (d.w.z. wat als sommige bestaande waarden slechts 5 tekens lang zijn)
Antwoord 3, Autoriteit 26%
Bovenstaande gebruikers genoemd, die oplossingen zijn erg basic en ze zullen niet werken als de database verschillende telefoonformaten bevat zoals:
(123)123-4564
123-456-4564
1234567989
etc
Hier is een complexere oplossing die werkt met een bepaalde input:
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
Antwoord 4, Autoriteit 21%
Ik raad u niet aan slechte gegevens in de database te houden en vervolgens alleen op de uitvoer te corrigeren. We hebben een database waar telefoonnummers in afwisseling worden ingevoerd als:
- (555) 555-5555
- 555 + 555 + 5555
- 555.555.5555
- (555) 555-5555
- 5555555555
Verschillende mensen in een organisatie kunnen verschillende ophaalfuncties en updates naar de database schrijven en daarom zou het moeilijker zijn om opmaak- en ophaalregels in te stellen. Ik corrigeer daarom de gegevens in de database eerst en vooral en vervolgens instellen in plaatsregels en vormde validaties die de integriteit van deze database beschermen die zich vooruitgeeft.
Ik zie geen rechtvaardiging voor het bijhouden van slechte gegevens, tenzij als een dubbele kolom wordt toegevoegd met gecorrigeerde opmaak en de oorspronkelijke gegevens die worden gehouden voor redundantie en referentie, en ja, ik beschouw slecht geformatteerde gegevens als slechte gegevens.
Antwoord 5, Autoriteit 17%
Oplossingen die SUBSTRINGen aaneenschakeling +zijn bijna onafhankelijk van RDBMS. Hier is een korte oplossing die specifiek is voor SQL Server:
declare @x int = 123456789
select stuff(stuff(@x, 4, 0, '-'), 8, 0, '-')
Antwoord 6, Autoriteit 10%
U kunt dit ook proberen:
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
einde
gebruik erop selecteren
(SELECT [dbo].[fn_FormatPhone](f.coffphone)) as 'Phone'
Uitvoer zal
. zijn
Antwoord 7, autoriteit 2%
Ik ontdekte dat dit werkt in een (123) – 456-7890 formaat.
UPDATE table
SET Phone_number = '(' +
SUBSTRING(Phone_number, 1, 3)
+ ') '
+ '- ' +
SUBSTRING(Phone_number, 4, 3)
+ '-' +
SUBSTRING(Phone_number, 7, 4)
Antwoord 8, autoriteit 2%
De functie van @sqiller voor mijn doeleinden bijgewerkt
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
Antwoord 9, autoriteit 2%
U kunt FORMATgebruiken als uw kolom een getal is. Syntaxis zoals
FORMAT ( waarde, formaat [, cultuur ] ) In gebruik zoals
FORMAT ( @d, 'D', 'en-US' )of FORMAT(123456789,'###-##-####')(Maar Dit werkt alleen voor SQL SERVER 2012 en daarna)
In gebruik zoals
UPDATE TABLE_NAME SET COLUMN_NAME = FORMAT(COLUMN_NAME ,'###-##-####')
En
als uw kolom Varchar of Nvarchar is, gebruik dan dit
CONCAT(SUBSTRING(CELLPHONE,0,4),' ',SUBSTRING(CELLPHONE,4,3),'',SUBSTRING(CELLPHONE,7,2) ,' ',SUBSTRING(CELLPHONE,9,2) )
Je kunt altijd hulp krijgen van
Antwoord 10
Als u alleen de uitvoer wilt formatteren, hoeft u geen nieuwe tabel of functie te maken. In dit scenario stond het netnummer op een apart veld. Ik gebruik field1, field2om te illustreren dat je andere velden in dezelfde zoekopdracht kunt selecteren:
area phone
213 8962102
Selecteer stelling:
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
Voorbeeld UITVOER:
columns: FIELD1, FIELD2, AREA, PHONE, FORMATTED PHONE
data: Field1, Field2, 213, 8962102, 213-896-2102