Function to generate random phone number

Question

I'm wondering how to create a function that generates random phone numbers in the format:

(888) 888-8888

Answer 1

Better with to_char()

SELECT to_char(random() * 10000000000, 'FM"("000") "000"-"0000');

Much simpler & faster than my original. Multiply the random number with 10^N to get N random digits before the comma. The FM modifier stands for "Fill Mode" and prevents padding with a leading space character. See:

• Display float column with leading sign

This simpler form works, too:

SELECT to_char(random() * 10000000000, 'FM(000) 000-0000');

Double-quotes are just the safe way to add constant strings to the output pattern (disables possible special meaning).

Original answer

SELECT format('(%s%s%s) %s%s%s-%s%s%s%s'
     , a[1], a[2], a[3], a[4], a[5], a[6], a[7], a[8], a[9], a[10])
FROM  (
   SELECT ARRAY (
      SELECT trunc(random() * 10)::int
      FROM   generate_series(1, 10)
      ) AS a
   ) sub;

Either returns:

(213) 633-4337

Or similar.

Answer 2

Not the most elegant of code but it's very simple so should give you a basis to work from:

SELECT '('
    || (RANDOM() * 9)::INT
    || (RANDOM() * 9)::INT
    || (RANDOM() * 9)::INT
    || ') '
    || (RANDOM() * 9)::INT
    || (RANDOM() * 9)::INT
    || (RANDOM() * 9)::INT
    || '-'
    || (RANDOM() * 9)::INT
    || (RANDOM() * 9)::INT
    || (RANDOM() * 9)::INT
    || (RANDOM() * 9)::INT;

Answer 3

I was thinking of this:

select replace(replace(replace('(@n1) @n2-@n3),
                               '@n1', lpad((random()*1000)::int::text, 3, '0')
                              ), '@n2', lpad((random()*1000)::int::text, 3, '0')
                      ), lpad((random()*10000)::int::text, 4, '0')
              )

That is, use three different random numbers, one for each grouping.

The arithmetic would be slightly different if you don't allow one or more elements to start with a zero.

Answer 4

select regexp_replace(CAST (random() AS text),'^0\.(\d{3})(\d{3})(\d{4}).*$','(\1)\2-\3') AS random;

The random() function is used to generate a random number between 0 and 1. It is then CAST as text and a regexp_replace is used to add the formatting characters.

Updated to pad the string with some arbitrary numbers:

select regexp_replace(rpad(CAST (random() AS text),12,CAST(random() AS text)),'^0\.(\d{3})(\d{3})(\d{4}).*$','(\1)\2-\3') AS random;

There is no guarantee this will produce valid phone numbers - for example area codes can't start with zero or one, but if you just need to fill in some numbers quickly, this should do the trick.