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
