去除字符串中的所有控制字符及去除 A到Z 之外的所有字符

IF OBJECT_ID('dbo.Fun_CutControlChars') IS NOT NULL
	DROP FUNCTION dbo.Fun_CutControlChars
GO
-- =============================================
-- Author:		yenange
-- Create date: 2018-06-07
-- Description:	去除字符串中的所有控制字符
-- =============================================
CREATE FUNCTION dbo.Fun_CutControlChars
(
	@str NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @r NVARCHAR(MAX),@s NCHAR(1)
	IF @str IS NULL
		RETURN NULL;
	IF @str=''
		RETURN ''; 
	
	SET @r=''
	WHILE LEN(@str)>0
	BEGIN
		SET @s=LEFT(@str,1);
		SET @str=SUBSTRING(@str,2,LEN(@str)-1);
		
		SET @r= @r + (CASE WHEN ASCII(@s)<=31 THEN '' ELSE @s END); 
	END

	RETURN @r
END
GO

DECLARE @s NVARCHAR(100),@s2 NVARCHAR(100);
SET @s='	12345abc
';
SET @s2=dbo.Fun_CutControlChars(@s);

SELECT @s,LEN(@s),@s2,LEN(@s2);
/*
 12345abc  	11	12345abc	8
*/

----去除 A 到 Z 之外的所有字符

IF OBJECT_ID('dbo.Fun_DeleteCharsButAToZ') IS NOT NULL
	DROP FUNCTION dbo.Fun_DeleteCharsButAToZ
GO
-- =============================================
-- Author:		yennage
-- Create date: 2018-07-17
-- Description:	删除除 A-Z 之外的所有字符
-- =============================================
CREATE FUNCTION dbo.Fun_DeleteCharsButAToZ
(
	@str NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @r NVARCHAR(MAX),@s NCHAR(1),@ascii INT
	IF @str IS NULL
		RETURN NULL;
	IF @str=''
		RETURN ''; 
	
	SET @r=''
	WHILE LEN(@str)>0
	BEGIN
		SET @s=LEFT(@str,1);
		SET @str=SUBSTRING(@str,2,LEN(@str)-1);
		SET @ascii=ASCII(@s);
		
		SET @r= @r + (CASE WHEN (@ascii>=65 AND @ascii<=90)		--'A' -> 'Z'
							OR  (@ascii>=97 AND @ascii<=122)	--'a' -> 'z'
							THEN @s ELSE '' END)
	END
 
	RETURN @r
END
GO
SELECT dbo.Fun_DeleteCharsButAToZ('a,b'),dbo.Fun_DeleteCharsButAToZ('a:b')