SQL function to return formatted list of phone numbers-CodePudding

Looking to build an SQL Server function that pulls a list of phone numbers from a table and formats them in a specific way so that they can be passed into a paging systems API.

The value returned could contain any number of phone numbers and each one needs to be wrapped in double quotes and separated by a comma.

The following function:

BEGIN
DECLARE @MyRESULT varchar(max)
DECLARE @PAGING_RESULTS nvarchar(max)
DECLARE MyCursor CURSOR FOR
SELECT PHONE_NUMBER FROM CONTACTS WHERE ON_CALL = 1
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @MyRESULT
WHILE @@FETCH_STATUS = 0
BEGIN
Set @PAGING_RESULTS = isnull(@PAGING_RESULTS, '')   '"+'   isnull(@MyRESULT, '')    '",'
FETCH NEXT FROM MyCursor INTO @MyRESULT
END
Close MyCursor
deallocate MyCursor
Set @PAGING_RESULTS = isnull(@PAGING_RESULTS, '')
return @PAGING_RESULTS
END

Is getting me close but it leaves me with an extra comma at the end of result that breaks the API call.

Is there a better way I can do this or additional work I can do in the function to clean the results.

Sample data:

Phone	On Call
1234567890	1
9876543210	1
7652341890	1

Ideal result:

"1234567890", "9876543210", “7652341890”

The current function does return my result set but there is an extra / invalid comma at the end, for example:

"1234567890", "9876543210", “7652341890”,

Many thanks!

CodePudding user response：

If you're using a fully supported version of SQL Server simply use string_agg()

select @Result = String_Agg(Concat('"', phone, '"'), ', ')
from Contacts
where on_call = 1;

On version 14 and prior you can use for xml path, and stuff removes the additional delimiter.

select @Result = Stuff(
(
    select ', '   Concat('"', phone, '"')
    from Contacts
    where on_call = 1
    for xml path('')
), 1, 1, '');

CodePudding user response：

Cursors are slow, don't use them for simple things.

DECLARE @MyRESULT varchar(max)=''
SELECT @MyRESULT = @MyRESULT   '"+'   ISNULL(PHONE_NUMBER, '')    '",'
FROM CONTACTS WHERE ON_CALL = 1
RETURN LEFT(@MyRESULT,LEN(@MyRESULT)-1)

When your MSSQL version is 2017 or higher:

DECLARE @MyRESULT VARCHAR(MAX)
SELECT @MyRESULT = STRING_AGG('+"' PHONE_NUMBER '"',',')
FROM CONTACTS WHERE ON_CALL=1