SELECT	MyPK,
	[Data] = dbo.KK_FN_strShowChar(MyColumn, default)
FROM	MyTable
	-- Restrict to rows with excluded characters (this example = all printable ASCII). use '%[^0-9]%' for integer numbers etc
WHERE	MyColumn LIKE '%[^ -~]%' COLLATE Latin1_General_BIN2

PRINT 'Create function KK_FN_strShowChar'
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[KK_FN_strShowChar]') AND xtype IN (N'FN', N'IF', N'TF'))
	DROP FUNCTION dbo.KK_FN_strShowChar
GO

CREATE FUNCTION dbo.KK_FN_strShowChar
(
	@strData		varchar(MAX),	-- String Data
	@intOptions		smallint = 0	-- [Unused, reserved for future expansion, pass as DEFAULT]
)
RETURNS varchar(MAX)
/* WITH ENCRYPTION */
AS
/*
 * KK_FN_strShowChar	Convert non-printable characters to [nn] to "reveal" them
 *
 * Returns:
 *
 * varchar(MAX)
 *
 * HISTORY:
 *
 * 24-Aug-2011 Started
 */
BEGIN
DECLARE	@intOffset		int,
	@strFindPattern	varchar(10),
	@strReplaceWith	varchar(10)

	SELECT	@strFindPattern = '%[^' + CHAR(33) + '-' + CHAR(127) + ']%',	-- Characters > SPACE and <= CHAR(127) are permitted
		@intOffset = PATINDEX(@strFindPattern, @strData COLLATE Latin1_General_BIN2)
	WHILE @intOffset > 0
	BEGIN
		SELECT	@strReplaceWith = '[' + CONVERT(varchar(3), ASCII(SUBSTRING(@strData, @intOffset, 1))) + ']',
			@strData = STUFF(@strData, @intOffset, 1, @strReplaceWith),
			@intOffset = PATINDEX(@strFindPattern, @strData COLLATE Latin1_General_BIN2)
	END

	RETURN @strData

/** TEST RIG

DECLARE	@TestData TABLE
(
	T_String	varchar(20) NULL,
	T_Description	varchar(30) NULL
)

INSERT INTO @TestData
(
	T_String, T_Description
)
SELECT	*
FROM
(
	SELECT	[T_String] = '!ABC;DEF<>xyz?', [T_Description] = '!ABC;DEF<>xyz?'
	UNION ALL
	SELECT	CHAR(9)+'TAB'+CHAR(13)+'CR'+CHAR(10)+'LF', '(9)TAB(13)CR(10)LF'
	UNION ALL
	SELECT	CHAR(9)+'Leading', '(9)Leading'
	UNION ALL
	SELECT	'Trailing'+CHAR(9), 'Trailing(9)'
	UNION ALL
	SELECT	CHAR(9), 'Just(9)'
	UNION ALL
	SELECT	CHAR(0)+'Leading', '(0)Leading'
	UNION ALL
	SELECT	'Trailing'+CHAR(0), 'Trailing(0)'
	UNION ALL
	SELECT	CHAR(0), 'Just(0)'
	UNION ALL
	SELECT	CHAR(163), 'GB Pound sign £ (163)'
) AS X

SELECT	T_Description,
	[KK_FN_strShowChar] = dbo.KK_FN_strShowChar(T_String, default)
	
FROM	@TestData

**/
--==================== KK_FN_strShowChar ====================--
END
GO
PRINT 'Create function KK_FN_strShowChar DONE'
GO