ASCII Character Groups in String

,

This function lets you accomplish some very difficult tasks with ease. If your server is case sensitive, you can still find all names entered in uppercase only. You can find comments containing extended ASCII characters. You can find which comments have formatting tabs and line feeds.

You can search on eight different classes of ASCII characters. You can search for their existence or absence in a string. Or you can pass null for the "don't care" classes.

The code is simple so you may redefine your own special categories such as "valid XML extended chars". The performance is acceptable for development and debugging conversions and upgrades.

This is really a swiss army knife in performing tasks that are nearly impossible otherwise.

Just create the function. The script at the end of the function's code will display how every ASCII character is categorized.

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.AsciiGroupsMatch') AND xtype IN (N'FN', N'IF', N'TF'))
	DROP FUNCTION dbo.AsciiGroupsMatch
GO

CREATE FUNCTION AsciiGroupsMatch
( -- set to 1 to check for IN, set to 0 to check for NOT IN, set to null for no checking
	@s varchar(max),
	@HasLetter bit,		-- used to easily check for either upper or lower.
	@HasUpperCaseLetter bit,
	@HasLowerCaseLetter bit,
	@HasNumber bit,
	@HasSpace bit,
	@HasOtherPrinting bit,

	@HasFormatting bit,
	@HasNonPrinting bit,
	@HasExtendedAscii bit
)
RETURNS bit
AS
BEGIN

/*
Written by Bill Talada
On a case-insensitive server how do you check for all uppercase or all lowercase letters in a column?
How do you check for extended ASCII characters in comments?

Letters		upper or lower case
UpperCase	A-Z
LowerCase	a-z
Numbers		0-9
Spaces		a space
Formatting	ascii 9-13, ht, lf, vt, ff, cr
NonPrinting ascii 0-8 and 14-31 and 127 and 255
ExtendedAscii ascii 128-255
*/

DECLARE
	@Answer bit,
	@NonPrinting bit,
	@Formatting bit,
	@Space bit,
	@OtherPrinting bit,
	@Number bit,
	@UpperCaseLetter bit,
	@LowerCaseLetter bit,
	@ExtendedAscii bit
;

SET @NonPrinting = 0;
SET @Formatting = 0;
SET @Space = 0;
SET @OtherPrinting = 0;
SET @Number = 0;
SET @UpperCaseLetter = 0;
SET @LowerCaseLetter = 0;
SET @ExtendedAscii = 0;

DECLARE
	@i int,
	@imax int,
	@c int;

SET @i = 1;
SET @imax = datalength(@s); -- damned len() function is deceptive

WHILE @i <= @imax
BEGIN
	SET @c = ascii(SUBSTRING(@s, @i, 1));

	if @c between 97 and 122
	BEGIN
		SET @LowerCaseLetter = 1;
		GOTO next;
	END

	if @c between 65 and 90
	BEGIN
		SET @UpperCaseLetter = 1;
		GOTO next;
	END

	if @c between 48 and 57
	BEGIN
		SET @number = 1;
		GOTO next;
	END

	if @c between 0 and 8
	BEGIN
		SET @NonPrinting = 1;
		GOTO next;
	END

	if @c between 9 and 13
	BEGIN
		SET @formatting = 1;
		GOTO next;
	END

	if @c between 14 and 31
	BEGIN
		SET @NonPrinting = 1;
		GOTO next;
	END

	if @c = 32
	BEGIN
		SET @Space = 1;
		GOTO next;
	END

	if @c between 33 and 47
	BEGIN
		SET @OtherPrinting = 1;
		GOTO next;
	END

	if @c between 58 and 64
	BEGIN
		SET @OtherPrinting = 1;
		GOTO next;
	END

	if @c between 91 and 96
	BEGIN
		SET @OtherPrinting = 1;
		GOTO next;
	END

	if @c between 123 and 126
	BEGIN
		SET @OtherPrinting = 1;
		GOTO next;
	END

	if @c = 127
	BEGIN
		SET @NonPrinting = 1;
		GOTO next;
	END

	if @c between 128 and 255
	BEGIN
		SET @ExtendedAscii = 1;
		GOTO next;
	END

next:
	SET @i = @i + 1;
END

/*
select
	@NonPrinting as NonPrinting,
	@Formatting as Formatting,
	@Space as Spaces,
	@OtherPrinting as OtherPrinting,
	@Number as Numbers,
	@Upper as UpperAlpha,
	@Lower as LowerAlpha,
	@UpperAscii as UpperAscii
*/
	-- not guilty until proven guilty
	SET @Answer = 1;
	
	IF @HasLetter = 1 and @UpperCaseLetter = 0 and @LowerCaseLetter = 0 SET @Answer = 0;
	IF @HasLetter = 0 and (@UpperCaseLetter = 1 or @LowerCaseLetter = 1) SET @Answer = 0;

	IF @HasUpperCaseLetter = 1 and @UpperCaseLetter = 0 SET @Answer = 0;
	IF @HasUpperCaseLetter = 0 and @UpperCaseLetter = 1 SET @Answer = 0;

	IF @HasLowerCaseLetter = 1 and @LowerCaseLetter = 0 SET @Answer = 0;
	IF @HasLowerCaseLetter = 0 and @LowerCaseLetter = 1 SET @Answer = 0;

	IF @HasNumber = 1 and @Number = 0 SET @Answer = 0;
	IF @HasNumber = 0 and @Number = 1 SET @Answer = 0;

	IF @HasNonPrinting = 1 and @NonPrinting = 0 SET @Answer = 0;
	IF @HasNonPrinting = 0 and @NonPrinting = 1 SET @Answer = 0;

	IF @HasFormatting = 1 and @Formatting = 0 SET @Answer = 0;
	IF @HasFormatting = 0 and @Formatting = 1 SET @Answer = 0;

	IF @HasSpace = 1 and @Space = 0 SET @Answer = 0;
	IF @HasSpace = 0 and @Space = 1 SET @Answer = 0;

	IF @HasOtherPrinting = 1 and @OtherPrinting = 0 SET @Answer = 0;
	IF @HasOtherPrinting = 0 and @OtherPrinting = 1 SET @Answer = 0;

	IF @HasExtendedAscii = 1 and @ExtendedAscii = 0 SET @Answer = 0;
	IF @HasExtendedAscii = 0 and @ExtendedAscii = 1 SET @Answer = 0;

	RETURN @Answer;
END
GO

------------------
-- This displays the definitions of all ascii characters.

declare @t table (i int, IsLetter bit, IsUpper bit, IsLower bit, IsNumber bit, IsSpace bit, IsOthrPrnt bit, IsFmting bit, IsNonPrt bit, IsExtAscii bit)

declare @i int;

set @i=0;
while @i <= 255
begin
	insert into @t
	select
		@i,
		dbo.AsciiGroupsMatch(CHAR(@i),1,null,null,0,0,0,0,0,0), -- letter
		dbo.AsciiGroupsMatch(CHAR(@i),1,1,0,0,0,0,0,0,0), -- upper
		dbo.AsciiGroupsMatch(CHAR(@i),1,0,1,0,0,0,0,0,0), -- lower
		dbo.AsciiGroupsMatch(CHAR(@i),0,0,0,1,0,0,0,0,0), -- number
		dbo.AsciiGroupsMatch(char(@i),0,0,0,0,1,0,0,0,0), -- space
		dbo.AsciiGroupsMatch(CHAR(@i),0,0,0,0,0,1,0,0,0), -- OtherPrinting
		dbo.AsciiGroupsMatch(CHAR(@i),0,0,0,0,0,0,1,0,0), -- Formatting
		dbo.AsciiGroupsMatch(CHAR(@i),0,0,0,0,0,0,0,1,0), -- NonPrinting
		dbo.AsciiGroupsMatch(CHAR(@i),0,0,0,0,0,0,0,0,1) -- ExtAscii

	set @i=@i+1;
end

select CHAR(i),* from @t;
GO

Rate

4 (2)

Share

Share

Rate

4 (2)