We receive requests from time to time for how you can properly format a phone number (after the fact) when a user has registered within DotNetNuke or using Dynamic Registration but the masked editor or formatting regular expression was never implemented at that time. I wanted to post a query we have used from time to time that can properly format the phone number data.
Just a quick disclaimer that I have used this query for a long time but I did not originally come up with it (or know who to give credit to for it). If you ever find the original author I would be happy to post their info…
Step 1: Create the SQL Server Function:
You can create this stored procedure under Host/SQL
Create FUNCTION [dbo].[func_DataSprings_FormatUSPhoneNumber]
(
@phonenumber VARCHAR(20)
)
RETURNS VARCHAR(20)
AS
BEGIN
/** Remove White Space and non-Integer(s) values **/
WHILE PATINDEX('%[^0-9]%', LTRIM(RTRIM(@phonenumber))) > 0
BEGIN
SET @phonenumber = REPLACE(@phonenumber, SUBSTRING(@phonenumber, PATINDEX('%[^0-9]%', @phonenumber),1), '')
END
SET @phonenumber = LTRIM(RTRIM(@phonenumber))
/** Get the number of Characters **/
DECLARE @NumChars INT
SET @NumChars = LEN(@phonenumber)
/** Check to see if this phone number starts with a '1' **/
IF @NumChars = 11 AND LEFT(@phonenumber,1) = '1'
BEGIN
SET @phonenumber = RIGHT(@phonenumber,10)
SET @NumChars = LEN(@phonenumber)
END
/** If valid number of digits proceed with formatting **/
IF @NumChars = 7 OR @NumChars = 10
BEGIN
SET @phonenumber = REVERSE(@phonenumber)
/** Format Phone Number **/
SET @phonenumber =
REVERSE(LEFT(@phonenumber,4)
+ '-'
+ SUBSTRING(@phonenumber,5,3)
+ COALESCE(' )'
+ NULLIF(SUBSTRING(@phonenumber,8,3),'') + '(', ''))
END
ELSE
SET @phonenumber = NULL
RETURN @phonenumber
END
Step 2: Determine what the profile field ID is for the Telephone field you want to modify. A great way to find this out is just to go to Host/SQL and execute something like this:
select PropertyDefinitionID, PropertyName from ProfilePropertyDefinition
Where PortalID = 0
Order By PropertyName
This is assuming you are using this on PortalID 0. You will see the PRopertyDefinitionID within the query… I believe the property definition for DotNetNuke is 33 for Telephone initially… and 34 for Cell initially.
Step 3: Make a backup of your database
Step 4: Run a query to utilize the formatting function and properly format the telephone number for all existing records. It would be executed under Host/SQL and you will want to check the box that says run as script (although it shouldn’t be necessary it will provide you with any error messages if there are any returned). The query would look like this:
Update UserProfile
SEt PropertyValue = dbo.func_DataSprings_FormatUSPhoneNumber(PropertyValue) from UserProfile
Where PropertyDefinitionID = 33
And PropertyValue <> ''
Step 5: I believe this data is heavily cached so you can clear cache by going to Host / Host Settings and clicking Clear Cache / Restart Application. If you are using Dynamic User Directory you will also want to kick off the Dynamic User Directory User Data Feed Scheduled Task/Process before that data will be displayed properly.
That is all!
Thanks,
Chad