Optimize function to take less time?

I have a column named comment in the student table, I added some random characters across these encrypted data (adding noise), I'm querying the encrypted data so I should clear this noise through a function called remove (this function removes every fourth letter from the encrypted string). I created a scalar-valued function named Remove which cleans some noise from the encrypted data. This function works correctly, but it takes a long time to execute, about 40 seconds (with a 1000000 records). So I tried with an inline table-valued function, but it returned no data.

An example of the data being queried is:

"!,T%OMAZTAA#:R$Tt$TNM)CRJEI6ZN/=#8AoABE1NAYABE-PI!0VAAj#G)sV)C]IZN/`)V

The scalar valued function is as follows

CREATE FUNCTION [dbo].[Remove] (@encrypt VARCHAR(255))
    RETURNS VARCHAR(200)
AS
BEGIN
    DECLARE @word VARCHAR(200) = '';

    WITH Numbers AS (
        SELECT TOP (LEN(@encrypt)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
        FROM master.dbo.spt_values
    )

    SELECT @word += SUBSTRING(@encrypt, N, 1)
    FROM Numbers
    WHERE (N - 1) % 4 <> 0;

    RETURN @word;
END;

When using in a select statement it works fine and returns data, but takes 40 seconds.

SELECT Id, COMMENT 
FROM student 
WHERE dbo.Remove(COMMENT) like '%N/#8AABEN`AABEPI!VAA#G)V)CIZN`)V';

However, by creating an inline table valued function as

CREATE FUNCTION [dbo].[Remove] (@StringData VARCHAR(255))
RETURNS TABLE
AS RETURN
(
    WITH Numbers AS (
        SELECT 1 AS N
        UNION ALL
        SELECT N + 1
        FROM Numbers
        WHERE N < LEN(@StringData)
    )
    SELECT
        word = STRING_AGG(SUBSTRING(@StringData, Numbers.N, 1), '') WITHIN GROUP (ORDER BY Numbers.N)
    FROM Numbers
    WHERE (Numbers.N - 1) % 4 <> 0
);

and applying

SELECT Id, COMMENT
FROM student
CROSS APPLY dbo.[Remove](COMMENT) AS r
WHERE r.word LIKE '%N/#8AABENAABEPI!VAA#G)V)CIZN)V';

It gives me error

The statement terminated. The maximum recursion 100 has been exhausted before statement completion

by trying to add OPTION(MAXRECURSION 0); as

SELECT Id, S_L_COMMENT_idx
FROM LINEITEM2 s
CROSS APPLY dbo.[Removess](S_L_COMMENT_idx) AS r
WHERE r.word LIKE '%N/#8AABENAABEPI!VAA#G)V)CIZN)V'
OPTION(MAXRECURSION 0);

it takes 4 minutes and also no results are returned.

Using:

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Home 10.0 (Build 22621: )

Solution

To improve performance and avoid the recursion limit, you can use a numbers table to generate the sequence of numbers instead of a recursive CTE

CREATE FUNCTION [dbo].[Remove] (@encrypt VARCHAR(255))
RETURNS TABLE
AS RETURN
(
    WITH Numbers AS (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
        FROM master.dbo.spt_values
    )
    SELECT
        word = STRING_AGG(SUBSTRING(@encrypt, Numbers.N, 1), '') WITHIN GROUP (ORDER BY Numbers.N)
    FROM Numbers
    WHERE (Numbers.N - 1) % 4 <> 0
      AND Numbers.N <= LEN(@encrypt)
);

I have used, ROW_NUMBER() over the spt_values table to generate a sequence of numbers. This should be more efficient than the recursive approach.

please use dbo.prefix before the function name:

SELECT Id, COMMENT
FROM student
CROSS APPLY dbo.[Remove](COMMENT) AS r
WHERE r.word LIKE '%N/#8AABENAABEPI!VAA#G)V)CIZN)V';