dbo.udf_array_sort.sql

USE [NBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*------------------------------------------------------------------------------
	Function Name: [dbo].[udf_array_sort]
	Purpose: Sorts elements in a delimited string array and returns a delimited string
    
	Project: T-SQL-Scripts
	Date Created: 2025-10-15
	Author: Ludwig Mark
    
	Parameters:
	@input - NVARCHAR(MAX) - Source array string
	@delimiter - NCHAR(1) - Delimiter character between elements
    
	Returns: NVARCHAR(MAX)
    
	Notes:
	- Trims elements and sorts them using default collation
	- Uses STRING_AGG WITHIN GROUP for ordering
	- Order is case-sensitive depending on database collation
    
	Performance:
	- Execution time: O(n log n) due to sorting
	- Memory usage: Proportional to number of elements
    
	Dependencies:
	- Requires STRING_SPLIT (SQL Server 2016+)
	- Requires STRING_AGG (SQL Server 2017+)
    
	Example:
	------------------------
	SELECT dbo.udf_array_sort('b;A;c;d', ';') AS Sorted
    
	Returns: 'A;b;c;d' (depending on collation)
    
	Modification History:
	Date        Author      Description
	----------  ----------  ------------------------------------------------
	2025-10-15  Ludwig M.   Initial creation
    
	Validation:
	- Returns NULL if @input is NULL
	- Handles empty elements correctly
    
	LICENSE: MIT
------------------------------------------------------------------------------*/

CREATE   FUNCTION [dbo].[udf_array_sort] (
	@input NVARCHAR(MAX)
	, @delimiter NCHAR(1)
) RETURNS NVARCHAR(MAX) AS 
BEGIN


	RETURN (
		SELECT STRING_AGG(value, @delimiter) WITHIN GROUP (ORDER BY value)
		FROM (
			SELECT TOP (100) PERCENT value = LTRIM(RTRIM(value))
			FROM STRING_SPLIT(@input, @delimiter)
			ORDER BY value
		) P
	)


END
GO