Is it possible to exclude CURSOR here?

Question

I have a scalar UDF that is responsible for returning NVARCHAR(MAX)with unique words . Do not ask why. :)

Sample input: "pizza pinapal salami pizza cheese cheese"

Sample yield: "salami cheese tweezers pizza."

Here's the UDF:

ALTER FUNCTION [dbo].[ToUniqueString]
(
    @NonUniqueString NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @ReturnValue NVARCHAR(MAX)

    -- Split the string by spaces.
    DECLARE @Words TABLE (Word NVARCHAR(MAX))
    INSERT INTO @Words
    SELECT Word FROM dbo.SplitText(@NonUniqueString , ' ')

    -- Cursor through the records, creating a unique string.
    DECLARE @CurrentWord NVARCHAR(MAX)
    DECLARE @UniqueString NVARCHAR(MAX) = ''

    DECLARE WordCursor CURSOR FOR SELECT DISTINCT Word FROM @Words
    OPEN WordCursor
    FETCH NEXT FROM WordCursor INTO @CurrentWord
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @UniqueString = @UniqueString + ' ' + @CurrentWord

        FETCH NEXT FROM WordCursor INTO @CurrentWord
    END
    CLOSE WordCursor
    DEALLOCATE WordCursor

    RETURN RTRIM(LTRIM(@UniqueString ))
END

Can this be done without a cursor? Is the cycle WHILEmore efficient? What about FOR XML?

Just find the most effective way to meet this requirement.

Answer 1

This can be done by replacing the cursor with FOR XML:

ALTER FUNCTION [dbo].[ToUniqueString] 
( 
    @NonUniqueString NVARCHAR(MAX) 
) 
RETURNS NVARCHAR(MAX) 
AS 
BEGIN 
    DECLARE @ReturnValue NVARCHAR(MAX) 

    -- Split the string by spaces. 
    DECLARE @Words TABLE (Word NVARCHAR(MAX)) 
    INSERT INTO @Words 
    SELECT Word FROM dbo.SplitText(@NonUniqueString , ' ') 

    DECLARE @UniqueString NVARCHAR(MAX) = '' 

    set @UniqueString = 
    stuff( 
        (SELECT Distinct  ' ' + Word
         FROM @Words 
         for xml path('')) 
    , 1, 1, '')   

    RETURN @UniqueString
END