User Defined Function with Recursive Common Table Expression

User defined functions suffer from inability to be query plan optimized, but this is a nice function I wrote to find ASCII control characters

CREATE FUNCTION [dbo].[ContainsControlCharacters] 
(
	@textToCheck NVARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
	DECLARE @invalidCharCount INT;
	-- Sadness that it came to this
	-- Splits the input text into characters
	-- Forces character to ASCII code value
	-- then finds all the code values (int) that are not within expected ASCII     
	-- charactes so Line Feeds etc are removed, but special chars like _! are still allowed
	-- https://theasciicode.com.ar/
	WITH [AsAscii] AS
	(
		SELECT 1 AS Number
			UNION ALL
		SELECT Number+1
			FROM AsAscii
			WHERE Number < LEN(@textToCheck)
	)
	SELECT @invalidCharCount = COUNT(*)
	FROM
	(
		SELECT ASCII(SUBSTRING(@textToCheck,Number,1)) as [Code]
		FROM [AsAscii]
	) as [AsciiCodes]    
	WHERE  [AsciiCodes].[Code] NOT BETWEEN 32 and 255
	OR [AsciiCodes].[Code] = 127

	RETURN CAST(@invalidCharCount AS BIT)
END
GO

Example Use

DROP TABLE IF EXISTS [#ExampleText]
CREATE TABLE [#ExampleText]
(
	TextToCheck NVARCHAR(200)
)

INSERT INTO [#ExampleText] (TextToCheck)
VALUES
	('This is good'),
	('This ís also gòòd'),
	(char(127) + ' this is not'),
	('nope ' + char(15)),
	(char(22) +' also no');

SELECT *
FROM [#ExampleText] as [e]
WHERE [dbo].[ContainsControlCharacters]([e].[TextToCheck]) = 0

Results

TextToCheck
This is good
This ís also gòòd