Random Phone number generator

Question

I'm trying to update a table, specifically the phone numbers.

I have made two procedures (also converted both to functions):

CreateRandomMobileNumber

    ALTER procedure [dbo].[GetRandomMobileNumber](
         @min AS BIGINT,
         @max AS BIGINT,
         @mobile_num AS VARCHAR(20)
    ) AS BEGIN
        SELECT @min = 1, @max = 99999999

        SELECT @mobile_num = CAST(CAST(((@max + 1) - @min) * Rand() + @min as bigint) as varchar(15))
        SELECT @mobile_num = '04' + ' ' + SUBSTRING(@mobile_num, 1, 4) + ' ' + RIGHT(@mobile_num,4)
        SELECT @mobile_num
    END

CreateRandomPhoneNumber

   ALTER procedure [dbo].[GetRandomPhoneNumber](
         @min AS BIGINT,
         @max AS BIGINT,
         @phone_num AS VARCHAR(20)
    ) 
    AS
    BEGIN
        SELECT @min = 1, @max = 99999999
        Select @phone_num = CAST(CAST(((@max + 1) - @min) * Rand() + @min as bigint) as varchar(15))
        SELECT @phone_num = 
            CASE 
                WHEN LEFT(@phone_num, 1) BETWEEN 1 AND 6 
                    THEN  '02' + ' ' + SUBSTRING(@phone_num, 1, 4) + ' ' + RIGHT(@phone_num,4)
                WHEN LEFT(@phone_num, 1) BETWEEN 7 AND 8
                    THEN  '03' + ' ' + SUBSTRING(@phone_num, 1, 4) + ' ' + RIGHT(@phone_num,4)
                    ELSE  '07' + ' ' + SUBSTRING(@phone_num, 1, 4) + ' ' + RIGHT(@phone_num,4)
            END
        SELECT @phone_num
    END

What I want to do is be able to call these on an update, this is the insert statement:

; WITH CTE AS
(

    SELECT *, rn = ROW_NUMBER() OVER ( ORDER BY newid() )
    FROM anon_PersonChangeData
)
UPDATE c1
SET   c1.personNewGender        = c2.personCurrentGender
    , c1.personNewFirstName     = c2.personCurrentFirstName
    , c1.personNewSurname       = c3.personCurrentSurname 
    , c1.personNewHomeEmail     = CASE 
                                WHEN c1.personStatus IN ('both', 'candidate') AND c1.personCurrentFirstName LIKE '%p%'
                                    THEN c2.personCurrentFirstName + '.' + c3.personCurrentSurname + '@yoohoo.com'
                                WHEN c1.personStatus IN ('both', 'candidate') AND c1.personCurrentFirstName LIKE '%e%'
                                    THEN c2.personCurrentFirstName + '.' + c3.personCurrentSurname + '@hatmail.com'
                                ELSE     c2.personCurrentFirstName + '.' + c3.personCurrentSurname + '@gmoil.com'
                              END
    , c1.personNewWorkEmail = NULL
    , c1.personNewHomePhone = dbo.GetRandomPhoneNumber
    , c1.personNewWorkPhone = NULL
    , c1.personNewMobilePhone = dbo.GetRandomMobileNumber
FROM CTE c1
    INNER JOIN CTE c2   ON c1.rn = c2.rn
    INNER JOIN CTE c3   ON c2.rn = c3.rn

I have tried a few different things and my function versions keep giving me the following error:

Invalid use of a side-effecting operator 'rand' within a function.

Any help would be much appreciated.

Possible duplicate of [Use RAND() in User Defined Function](https://stackoverflow.com/questions/31468836/use-rand-in-user-defined-function) — STLDev, Oct 03 '17 at 23:24
Note in particular the accepted answer of https://stackoverflow.com/questions/31468836/use-rand-in-user-defined-function Base randomness on newid() and **create a view** that you call within your functions — Paul Maxwell, Oct 03 '17 at 23:49
Thanks for the reply, I'm sticking with the view option rather than newID, as they are phone numbers and I don't need random so much as different from the original. — Owain Esau, Oct 04 '17 at 00:02

score 4 · Accepted Answer · answered Oct 03 '17 at 23:48

Here's an easy way, using an "inline tally table" to set the how many numbers you want to generate and ABS(CHECKSUM(NEWID())) % N to generate random numbers. In this case, I'm setting the bound between 1000000000 and 9999999999 (because we want 10 digits...

WITH 
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)), 
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),     -- 100 rows
    cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),     -- 10,000 rows
    cte_Tally (n) AS (
        SELECT TOP 20 -- set the number of row you wanr here...
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM
            cte_n3 a CROSS JOIN cte_n3 b                            -- 100,000,000 rows
        )
SELECT 
    PhoneFormatted = '(' + STUFF(STUFF(pn.PhoneNumber, 7, 0, '-'), 4, 0, ') ')
FROM
    cte_Tally
    CROSS APPLY ( VALUES (CAST(ABS(CHECKSUM(NEWID())) % 9999999999 + 1000000001 AS VARCHAR(14))) ) pn (PhoneNumber);

And the results...

PhoneFormatted
-------------------
(165) 103-7444
(288) 558-8646
(259) 635-8073
(277) 171-6645
(104) 265-1071
(119) 838-5696
(111) 378-0002
(202) 490-0627
(208) 400-6605
(101) 681-2601
(239) 842-7079
(257) 067-0305
(227) 761-1426
(171) 330-1550
(312) 728-6722
(108) 526-2654
(202) 573-9529
(205) 334-9711
(112) 345-8447
(202) 587-9164

The best part... It's fast... Here is the STATISTICS IO,TIME from putting 1M numbers into a temp table.

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 7 ms.

 SQL Server Execution Times:
   CPU time = 1392 ms,  elapsed time = 1224 ms.

(1000000 rows affected)

Thanks for your reply, ill give you the accepted answer because this does work well. I already used the rand view to create the random numbers as per the comments above. — Owain Esau, Oct 04 '17 at 00:00

score 2 · Answer 2 · answered Jan 16 '19 at 19:51

If you want a one liner, something like this might work:

SELECT TOP 500 PhoneNumber = FORMAT(ROUND(((9999999999 - 1111111111)*RAND(CAST(DATEDIFF(s,'1970-01-01 12:00:00',GETDATE()) As BIGINT)+(ROW_NUMBER() OVER(ORDER BY (SELECT(NULL)))) ) + 1111111111),0),'(###) ###-####')
FROM anytable;

Random Phone number generator

2 Answers2