E
You need to get to grips with https://en.wikipedia.org/wiki/Regular_expression s here. Now, PostgreSQL provides a https://www.postgresql.org/docs/9.3/functions-matching.html in this area and it is well beyond the scope of an answer here to teach everything about regexes - it's an entire area of computer science in itself - https://www.regular-expressions.info/ is one of the better tutorial sites IMHO.
First things first - a phone number is a string (TEXT in PostgreSQL dialect - VARCHAR() in others) - one doesn't add, subtract, multiply or divide phone numbers. Futhermore, characters other than 0-9 may appear in them (i.e. (,),-, . and space).
As @LaurenzAlbe points out, it's good to have a clear idea of the actual requirement. So, you want a check on your strings to enure that they correspond to a North American phone number (all of the code below is available on the fiddle https://dbfiddle.uk/?rdbms=postgres_13&fiddle=cb341453b59a0988b6f932168ef37063 )!
A North American phone number is 10 digits long, normally separated by a space into three groups of 3, 3 and 4 digits.
So, you can, as a first approximation, do something like this:
CREATE TABLE phnum_1
(
num TEXT NOT NULL
CONSTRAINT num_1_ck_1
CHECK (num ~ '^\d{3} \d{3} \d{4}$')
CONSTRAINT num_1_ck_2
CHECK (num ~ '^[0-9]{3} [0-9]{3} [0-9]{4}$')
CONSTRAINT num_1_ck_3
CHECK (num ~ '^[[:digit:]]{3} [[:digit:]]{3} [[:digit:]]{4}$')
);
Note that all three of these CONSTRAINTs do the same thing - just expressed differently.
These are very simple regexes (see below) and theyu just ensure that every entry for num must start with 3 digits followed by a space followed by 4 digits, another space and 4 final digits.
Explanation of the regex:
^ - is an "anchor" - it refers to the start of the string to be checked.
\d (or [0-9] or [[:digit:]]) is shorthand for a numerical character - (i.e. 0, 1, 2... , 9)
{n} is the way of saying n and only n occurrences of the previous thing - in this case, a digit - you can say, for example, {2,4} which means 2 to 4 occurrences of your match. In the above, you could use a redundant {3,3}/{4,4} if you wanted?
then a space - the literal space character - it has no special meaning in regexes.
then \d{4} \d{4} - 4 digits, a space and then 4 more digits.
finally, another anchor - the $ character which is the end of the string marker!
So, as you can see from the fiddle, '123 345 3434' is accepted as is 987 654 3210, but '123-234-5678' is rejected.
So, this regex is simple, but very restrictive. Now, the levels of complexity for this particular requirement can become stratospheric very quickly...
There are opening and closing brackets (( or )) (intl code), + or nothing before the intl code), is the local exchange code a valid one, a hyphen (-) or not between groups of digits? Really, the possibilities are almost endless...
I would urge you to take a look https://stackoverflow.com/questions/16699007/regular-expression-to-match-standard-10-digit-phone-number/16702965 to see how some/many of these thorny issues have been dealt with on our sister site StackOverflow.
I've put some of these regular expressions into the fiddle as follows:
CREATE TABLE phnum_2
(
num TEXT NOT NULL
);
and populate it with some sample potential numbers:
INSERT INTO phnum_2 VALUES
('123-456-7890'),
('987 654 3210'),
('123-234-5678'),
('+1 123 456 7890'),
('+353 123 456 7890');
And then run this query:
SELECT
num ~ '\(?\d{3}\)?[\s.-]\d{3}[\s.-]\d{4}$' AS re1,
num ~ '^(\+\d{1,2}\s)?((\(\d{3}\))|(\d{3}))[\s.-]\d{3}[\s.-]\d{4}$' AS re2,
num ~ '^\s*(?:\+?(\d{1,3}))?[-. (]*(\d{3})[-. )]*(\d{3})[-. ]*(\d{4})(?: *x(\d+))?\s*$' AS re3,
num ~ '^(\+\d{1,2}\s?)?1?\-?\.?\s?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}$' AS re4,
num ~ '^(\+1\s?)?1?\-?\.?\s?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}$' AS re5,
num ~ '^\s*(?:\+?(\d{1,3}))?[-. (]*(\d{3})[-. )]*(\d{3})[-. ]*(\d{4})(?: *x(\d+))?\s*$' AS re6,
num ~ '(\+\d{1,3}\s?)?((\(\d{3}\)\s?)|(\d{3})(\s|-?))(\d{3}(\s|-?))(\d{4})(\s?(([E|e]xt[:|.|]?)|x|X)(\s?\d+))?' AS re7
FROM
phnum_2;
Result:
re1 re2 re3 re4 re5 re6 re7
t t t t t t t
t t t t t t t
t t t t t t t
t t t t t t t
t f t f f t t
I would urge you to take a look at the ones which fail, and try to figure out why they've failed!
A couple of slightly less sophisticated ones are available https://stackoverflow.com/questions/33158774/regex-validation-for-north-american-phone-numbers , a more complex thread can be found https://stackoverflow.com/questions/123559/how-to-validate-phone-numbers-using-regex and https://regexlib.com/Search.aspx?k=us%20phone&c=-1&m=-1&ps=20&p=3&AspxAutoDetectCookieSupport=1 provides lots of possibilities, including https://regexlib.com/REDetails.aspx?regexp_id=18143 :
^(\+?1(-|\.|\s)?)?((\(((8(00|22|33|44|55|66|77|[8[0-9]))|900)\)|((8(00|22|33|44|55|66|77|[8[0-9]))|900))(-|\.|\s)?\d{3}(-|\.|\s)?\d{4}|(\([2-9]([02-9]\d|1[02-9])\)|[2-9]([02-9]\d|1[02-9]))(-|\.|\s)?[2-9]([02-9]\d|1[02-9](-|\.|\s)?\d{4}))$
However, I'll leave the final word to this contributor who https://stackoverflow.com/a/1245990/470530 that:
If the users want to give you their phone numbers, then trust them to
get it right. If they do not want to give it to you then forcing them
to enter a valid number will either send them to a competitor's site
or make them enter a random string that fits your regex. I might even
be tempted to look up the number of a premium rate horoscope hotline
and enter that instead.
I would also consider any of the following as valid entries on a web
site:
"123 456 7890 until 6pm, then 098 765 4321"
"123 456 7890 or try my
mobile on 098 765 4321"
"ex-directory - mind your own business"
Also, don't forget that regexes are expensive in terms of processing power - see https://blog.codinghorror.com/regex-performance/ from one of the founders of StackExchange and how to (partially) mitigate this from https://stackoverflow.com/a/34675820/470530 on StackOverflow.
So, you really should consider your requirements - and how the eventual answer is going to be stored - as free text or strictly as a series of 10 [valid] digits? The cleaner your data is in your tables, the more potential you'll have for using optimising indexing strategies.
