CODEWITHSUNDEEP
sqlsql-server-2008t-sql
user153923
user153923

Reputation:

PATINDEX test for phone number format

I have phone fields in the database like the following:

phone_num VARCHAR(50) NULL

It looks like just about all of these phone fields are 10 characters or less. If they are 10 characters, I will format them in the form of (xxx) xxx-xxxx.

CREATE FUNCTION [dbo].[FormatPhone](@Phone NVARCHAR(50)) RETURNS NVARCHAR(50) AS BEGIN
  declare @fone NVARCHAR(50), @len INT;
  set @fone=LTRIM(RTRIM(ISNULL(@Phone,'')));
  set @len = LEN(@fone);
  return case
    when @len=10 then
        '(' + SUBSTRING(@fone, 1, 3) + ') ' +
         SUBSTRING(@fone, 4, 3) + '-' + 
         SUBSTRING(@fone, 7, 4)
    when 10<@len then
        '(' + SUBSTRING(@fone, 1, 3) + ') ' + 
         SUBSTRING(@fone, 4, 3) + '-' + 
         SUBSTRING(@fone, 7, 4) + 'x' + 
         SUBSTRING(@fone, 11, @len)
    else
        @fone
    end;
END

The @fone variable eliminates NULL values and leading/trailing blank spaces.

Our software only inputs phone numbers using the numeric values.

However, there is nothing to prevent one of the many administrators at any of our customer's sites from manually executing some SQL to insert or update a record.

They could see the format we use, and try manually entering that data.

Or, they could try entering it as xxx.xxx.xxxx, or ...???? Who knows?

What is a good way to search my @fone variable for any indication that it has already been modified? Is PATINDEX best?

Upvotes: 0

Views: 1181

Answers (2)

user153923
user153923

Reputation:

Gordon's answer would be great if I were allowed to go in and add a constraint.

Since I am not, I ended up by creating the following FormatPhone function for our database:

-- =============================================
-- Author:      jp2code
-- Create date: 09 June 2016
-- Description: Formats a integer phone number value.
-- =============================================
CREATE FUNCTION [dbo].[FormatPhone](@Phone NVARCHAR(50)) RETURNS NVARCHAR(50) AS BEGIN
    declare @fone NVARCHAR(50), @len INT;
    set @fone=LTRIM(RTRIM(ISNULL(@Phone,'')));
    set @len = LEN(@fone);
    if ISNUMERIC(@fone + '.0e0')=1 begin
        return case
            when @len=7 then
                SUBSTRING(@fone, 1, 3) + '-' + 
                SUBSTRING(@fone, 4, 4)
            when @len=8 then
                SUBSTRING(@fone, 1, 1) + '-' + 
                SUBSTRING(@fone, 2, 3) + '-' + 
                SUBSTRING(@fone, 5, 4)
            when @len=9 then
                SUBSTRING(@fone, 1, 3) + '-' + 
                SUBSTRING(@fone, 4, 4) + ' x' + 
                SUBSTRING(@fone, 8, 2)
            when @len=10 then
                '(' + SUBSTRING(@fone, 1, 3) + ') ' + 
                SUBSTRING(@fone, 4, 3) + '-' + 
                SUBSTRING(@fone, 7, 4)
            when 10<@len then
                '(' + SUBSTRING(@fone, 1, 3) + ') ' + 
                SUBSTRING(@fone, 4, 3) + '-' + 
                SUBSTRING(@fone, 7, 4) + ' x' + 
                SUBSTRING(@fone, 11, @len)
            else
                @fone
            end;
    end
    return @fone;
END

Feel free to use this or modify it as you see fit.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Hmmm, after fixing all the numbers, I think you want a check constraint:

alter table t
    add constraint chk_t_phone_num
        check (phone_num like '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]);

This will ensure that the values put into the database are correct.

Upvotes: 2

Related Questions