Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Find non-printable characters in a column
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22858 Posts

Posted - 08/24/2011 :  04:38:33  Show Profile  Reply with Quote
Not the most efficient way to do this, but can be used to find rogue characters - such as line-break, tab, hard-space, or things like MDash (that may "look" fine when displayed). The function will convert them to "[ ASCII value ]" in the string so they can be displayed and debugged.

Example usage:

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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000