Replacement for char and ascii functions in natively compiled stored procedures

Question

Replacement for char and ascii functions in natively compiled stored procedures

SSC Enthusiast

Points: 181

February 4, 2023 at 11:02 pm

I am trying to create a natively compiled stored procedure where I need to check if a string contains a non-printable character (char(1) to char(31)). The problem is that function CHAR() is not supported in natively compiled stored procedures. I get the following error when creating the procedure:

The function 'char' is not supported with natively compiled modules.

Is there a way around or even a better way to do what I'm trying to?

Thanks

declare @string varchar(255);
declare @id tinyint;
declare @found bit;

set @string = 'My string';
set @len = len(@string);

select
@id = 1,
@found = 0;

while ( @id < @len and @found = 0 )
begin
if ( substring(@string,@id,1) in (
char(1), char(2), char(3), char(4), char(5), char(6), char(7), char(8),
char(9), char(10), char(11), char(12), char(13), char(14), char(15), char(16),
char(17), char(18), char(19), char(20), char(21), char(22), char(23), char(24),
char(25), char(26), char(27), char(28), char(29), char(30), char(31) )
)
set @found = 1;
else
set @id += 1;
end;

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

MullerN wrote:

Is there a way around or even a better way to do what I'm trying to?

What is it that you're ultimately trying to do?

Yeah... I can see you're looping through a string one character at a time and the loop stops as soon as you find one of the control characters but what are you going to do with that string once that's done? Are you going to replace the found character(s) with nothing or what?

In any case, you don't need a While loop for any of this. There's a much better way to do it using a "Tally" or "Numbers" function but, before we get into all of that, we need to know what the end game is here. 😀

p.s. Leave it to MS to build a race car that's missing a wheel. I can't believe that a natively compiled proc doesn't allow for Char/Ascii functions. Still, if it did, I wouldn't slow down a natively compiled proc with a WHILE loop. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MullerN SSC Enthusiast Points: 181 More actions · Answer 2

I actually didn't think of the tally table which will speed up performance, no doubt. My problem is that I need to identify all the non-printable characters and replace them with a printable character of my choice. However I haven't found a way to identify non-printable characters in a natively compiled stored procedure.

p.s. Yes, MS left a wheel out on this one. Char/Ascii functions are not supported in natively compiled stored procedure.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

MullerN wrote:

I actually didn't think of the tally table which will speed up performance, no doubt. My problem is that I need to identify all the non-printable characters and replace them with a printable character of my choice. However I haven't found a way to identify non-printable characters in a natively compiled stored procedure.
p.s. Yes, MS left a wheel out on this one. Char/Ascii functions are not supported in natively compiled stored procedure.

Although I still wouldn't use a While Loop, could you pass in a string of characters containing all the characters from CHAR(1) thru CHAR(31) as a parameter and use a LIKE? Maybe even hard-code such a string into the proc at design time?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101097 More actions · Answer 4

I think this code follows all the restrictions for natively compiled procs:

declare @byte tinyint;
declare @bad_chars char(33);
declare @new_string varchar(255);
declare @string varchar(255);
declare @string_len tinyint;
declare @string_replacement_char char(1);

set @bad_chars = '[' + char(01) + char(02) + + char(03) + char(04) + char(05) + char(06) + char(07) + char(08) + char(09) + char(10) +
    char(11) + char(12) + + char(13) + char(14) + char(15) + char(16) + char(17) + char(18) + char(19) + char(20) +
    char(21) + char(22) + + char(23) + char(24) + char(25) + char(26) + char(27) + char(28) + char(29) + char(30) +
    char(31) + ']';

set @string = 'sample string with ' + char(17) + char(22) + char(31) + ' 3 bad chars.';
set @string_len = LEN(@string);
set @new_string = '';
set @string_replacement_char = '?';

set @byte = 1;
while @byte <= @string_len
begin
    set @new_string = @new_string + case when substring(@string, @byte, 1) like @bad_chars then @string_replacement_char else substring(@string, @byte, 1) end
    set @byte = @byte + 1
end /*while*/
select @string, @new_string

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

Can you use a Tally function in natively compiled procs? If not, can you build an inline Tally cte? If so, I'd get rid of that bloody while loop.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101097 More actions · Answer 6

So go ahead and do it. Not sure specifically how a tally table is going to help here. Besides, a tight loop should likely perform well enough on a string that relatively short.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

ScottPletcher wrote:

So go ahead and do it. Not sure specifically how a tally table is going to help here. Besides, a tight loop should likely perform well enough on a string that relatively short.

His previous error message also stated that CHAR() is simply not allowed so not sure how your code is going to be compliant.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 8

@mullern,

I guess at this point, I have to ask, why does this need to be done as a Natively Compiled Proc to begin with. The use of a Tally table with ASCII() conversions to work with a numeric range instead of messing around with character based stuff is going to make this a whole lot faster.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 9

You can use LIKE in natively-compiled procedures, and that's enough:

-- Declare 2 strings: one with a bad character, and one without
DECLARE @BadChar varchar(128) = 'A funky char -->' + char(30);
DECLARE @NoBadChar varchar(128) = 'No funky char 12345';

DECLARE @string varchar(255), @id tinyint = 1;
DECLARE @found bit = 0, @len int = 0;

-- Test 1: string with bad character
SELECT @string = @BadChar, @len = len(@BadChar);
WHILE @id < @len and @found = 0 
  BEGIN
    -- The three-part test below transalates to:
    -- "If the character 
    --    - is not a space (!= ' '), AND
    --    - is not a letter (LIKE '[^A-z]'), AND
    --    - is not a number (LIKE '[^0-9]'),
    --  ...then it not a a valid character"
    IF substring(@string,@id,1) != ' ' AND substring(@string,@id,1) LIKE '[^A-z]' AND substring(@string,@id,1) LIKE '[^0-9]'
      BEGIN
        SELECT @found = 1;
        PRINT 'Found a bad char in @BadChar';
      END;
    SELECT @id += 1;
  END;

-- Test 2: string without bad character
SELECT @string = @NoBadChar, @len = len(@NoBadChar);
WHILE @id < @len and @found = 0 
  BEGIN
    IF substring(@string,@id,1) != ' ' AND substring(@string,@id,1) LIKE '[^A-z]' AND substring(@string,@id,1) LIKE '[^0-9]'
      BEGIN
        SELECT @found = 1;
        PRINT 'Found a bad char in @NoBadChar';
      END;
    SELECT @id += 1;
  END;

If other characters are possible good values ("$", ".", ",", "@", etc.), then you'll need to add additional tests to the three-part test in the code.

Eddie Wuerch
MCM: SQL

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 10

For natively compiled - what I would do is build a string using CHAR with all of the characters you want to search for and a second string with all of the replacement characters and just hardcode that into a TRANSLATE in the natively compiled code.

The code becomes very simple then:

CREATE PROCEDURE CleanString (@InputString varchar(8000))
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION
AS BEGIN ATOMIC WITH
(
    TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)
SELECT CleanString = TRANSLATE(@InputString, '', ';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;');
END

Here - the second parameter will be your constructed string of all invalid characters. It would probably be much easier to use the CHAR function to generate the string and then use dynamic SQL to build the procedure statement.

Declare @invalidCharacters varchar(40) = ''
      , @validCharacters varchar(40) = '';

 Select @invalidCharacters = string_agg(char(c.a), '')
      , @validCharacters = string_agg(';', '')
   From (Values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
              , (11), (12), (13), (14), (15), (16), (17), (18), (19), (20)
              , (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31)) As c(a)

Declare @sqlCommand nvarchar(max) = ' CREATE PROCEDURE CleanString (@InputString varchar(8000))
   WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION
     AS BEGIN ATOMIC WITH
        (
            TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N''us_english''
        )
            SELECT CleanString = TRANSLATE(@InputString, ' + quotename(@invalidCharacters, char(39)) + ', ' + quotename(@validCharacters, char(39)) + ');
          END'

  Print @sqlCommand;

I would start with something like this and adjust the hard-coding as needed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

MullerN SSC Enthusiast Points: 181 More actions · Answer 11

Thanks everyone. I actually solved the problem by creating a memory-optimized table storing the ASCII table and querying it inside the natively-compiled stored procedure. I can add additional fields to the Ascii_MO table if so needed, such as isUpper, isLower, or whether I want to parse a CSV file, in which case I would add isComma, isSingleQuote, isDoubleQuote.

The results of the code and example below is:

First query:

Row 1: asciiChar = 'a', asciiDecimal = 97 and asciiDescription = 'Small a'

Row 2: asciiChar = Nothing shows , asciiDecimal = 9 and asciiDescription = 'Horizontal tab'

Row 3: asciiChar = 'a', asciiDecimal = 97 and asciiDescription = 'Small a'

Second query, first non-printable character (tab):

id = 2

create table Ascii_MO (
id smallint identity (1,1) not null,
asciiChar char(1) not null,
asciiDecimal tinyint not null,
asciiHex char(2) not null,
asciiHtml varchar(6) not null,
asciiDescription varchar(50) null,
isPrintable bit,
isNonPrintable bit,
isAlpha bit,
isNumber bit,
constraint pk_Ascii_MO primary key nonclustered ( id ),
index inx_Ascii_MO_isPrintable nonclustered ( isPrintable ),
index inx_Ascii_MO_isNonPrintable nonclustered ( isNonPrintable ),
index inx_Ascii_MO_isAlpha nonclustered ( isAlpha ),
index inx_Ascii_MO_isNumber nonclustered ( isNumber )
) with ( memory_optimized = on, durability = schema_and_data );
go

create type Type_Tally_MO as table (
id int not null primary key nonclustered ( id ),
charValue char(1)
) with ( memory_optimized = on );
go

insert Ascii_MO (
asciiChar, asciiDecimal, asciiHex, asciiHtml, asciiDescription, isPrintable, isNonPrintable, isAlpha, isNumber
) values
( char(0), ascii(char(0)), '00', '&#0', 'Null', 0,1,0,0 ),
( char(1), ascii(char(1)), '01', '&#1', 'Start of header', 0,1,0,0 ),
( char(2), ascii(char(2)), '02', '&#2', 'Start of text', 0,1,0,0 ),
( char(3), ascii(char(3)), '03', '&#3', 'End of text', 0,1,0,0 ),
( char(4), ascii(char(4)), '04', '&#4', 'End of transmission', 0,1,0,0 ),
( char(5), ascii(char(5)), '05', '&#5', 'Enquiry', 0,1,0,0 ),
( char(6), ascii(char(6)), '06', '&#6', 'Acknowledge', 0,1,0,0 ),
( char(7), ascii(char(7)), '07', '&#7', 'Bell', 0,1,0,0 ),
( char(8), ascii(char(8)), '08', '&#8', 'Backspace', 0,1,0,0 ),
( char(9), ascii(char(9)), '09', '&#9', 'Horizontal tab', 0,1,0,0 ),
( char(10), ascii(char(10)), '0A', '&#10', 'Line feed', 0,1,0,0 ),
( char(11), ascii(char(11)), '0B', '&#11', 'Vertical tab', 0,1,0,0 ),
( char(12), ascii(char(12)), '0C', '&#12', 'Form feed', 0,1,0,0 ),
( char(13), ascii(char(13)), '0D', '&#13', 'Carriage return', 0,1,0,0 ),
( char(14), ascii(char(14)), '0E', '&#14', 'Shift out', 0,1,0,0 ),
( char(15), ascii(char(15)), '0F', '&#15', 'Shift in', 0,1,0,0 ),
( char(16), ascii(char(16)), '10', '&#16', 'Data link escape', 0,1,0,0 ),
( char(17), ascii(char(17)), '11', '&#17', 'Device control 1', 0,1,0,0 ),
( char(18), ascii(char(18)), '12', '&#18', 'Device control 2', 0,1,0,0 ),
( char(19), ascii(char(19)), '13', '&#19', 'Device control 3', 0,1,0,0 ),
( char(20), ascii(char(20)), '14', '&#20', 'Device control 4', 0,1,0,0 ),
( char(21), ascii(char(21)), '15', '&#21', 'Negative acknowledge', 0,1,0,0 ),
( char(22), ascii(char(22)), '16', '&#22', 'Synchronize', 0,1,0,0 ),
( char(23), ascii(char(23)), '17', '&#23', 'End of transmission block', 0,1,0,0 ),
( char(24), ascii(char(24)), '18', '&#24', 'Cancel', 0,1,0,0 ),
( char(25), ascii(char(25)), '19', '&#25', 'End of medium', 0,1,0,0 ),
( char(26), ascii(char(26)), '1A', '&#26', 'Substitute', 0,1,0,0 ),
( char(27), ascii(char(27)), '1B', '&#27', 'Escape', 0,1,0,0 ),
( char(28), ascii(char(28)), '1C', '&#28', 'File separator', 0,1,0,0 ),
( char(29), ascii(char(29)), '1D', '&#29', 'Group separator', 0,1,0,0 ),
( char(30), ascii(char(30)), '1E', '&#30', 'Record separator', 0,1,0,0 ),
( char(31), ascii(char(31)), '1F', '&#31', 'Unit separator', 0,1,0,0 ),
( char(32), ascii(char(32)), '20', '&#32', 'Space', 1,0,0,0 ),
( char(33), ascii(char(33)), '21', '&#33', 'Exclamation mark', 1,0,0,0 ),
( char(34), ascii(char(34)), '22', '&#34', 'Double quote', 1,0,0,0 ),
( char(35), ascii(char(35)), '23', '&#35', 'Number sign', 1,0,0,0 ),
( char(36), ascii(char(36)), '24', '&#36', 'Dollar sign', 1,0,0,0 ),
( char(37), ascii(char(37)), '25', '&#37', 'Percent', 1,0,0,0 ),
( char(38), ascii(char(38)), '26', '&#38', 'Ampersand', 1,0,0,0 ),
( char(39), ascii(char(39)), '27', '&#39', 'Single quote', 1,0,0,0 ),
( char(40), ascii(char(40)), '28', '&#40', 'Left parenthesis', 1,0,0,0 ),
( char(41), ascii(char(41)), '29', '&#41', 'Right parenthesis', 1,0,0,0 ),
( char(42), ascii(char(42)), '2A', '&#42', 'Asterisk', 1,0,0,0 ),
( char(43), ascii(char(43)), '2B', '&#43', 'Plus sign', 1,0,0,0 ),
( char(44), ascii(char(44)), '2C', '&#44', 'Comma', 1,0,0,0 ),
( char(45), ascii(char(45)), '2D', '&#45', 'Negative sign', 1,0,0,0 ),
( char(46), ascii(char(46)), '2E', '&#46', 'Period', 1,0,0,0 ),
( char(47), ascii(char(47)), '2F', '&#47', 'Slash', 1,0,0,0 ),
( char(48), ascii(char(48)), '30', '&#48', 'Zero', 1,0,0,0 ),
( char(49), ascii(char(49)), '31', '&#49', 'One', 1,0,0,0 ),
( char(50), ascii(char(50)), '32', '&#50', 'Two', 1,0,0,0 ),
( char(51), ascii(char(51)), '33', '&#51', 'Three', 1,0,0,0 ),
( char(52), ascii(char(52)), '34', '&#52', 'Four', 1,0,0,0 ),
( char(53), ascii(char(53)), '35', '&#53', 'Five', 1,0,0,0 ),
( char(54), ascii(char(54)), '36', '&#54', 'Six', 1,0,0,0 ),
( char(55), ascii(char(55)), '37', '&#55', 'Seven', 1,0,0,0 ),
( char(56), ascii(char(56)), '38', '&#56', 'Eight', 1,0,0,0 ),
( char(57), ascii(char(57)), '39', '&#57', 'Nine', 1,0,0,0 ),
( char(58), ascii(char(58)), '3A', '&#58', 'Colon', 1,0,0,0 ),
( char(59), ascii(char(59)), '3B', '&#59', 'Semicolon', 1,0,0,0 ),
( char(60), ascii(char(60)), '3C', '&#60', 'Less than', 1,0,0,0 ),
( char(61), ascii(char(61)), '3D', '&#61', 'Equal', 1,0,0,0 ),
( char(62), ascii(char(62)), '3E', '&#62', 'Greater than', 1,0,0,0 ),
( char(63), ascii(char(63)), '3F', '&#63', 'Question mark', 1,0,0,0 ),
( char(64), ascii(char(64)), '40', '&#64', 'At sign', 1,0,0,0 ),
( char(65), ascii(char(65)), '41', '&#65', 'Capital A', 1,0,0,0 ), 
( char(66), ascii(char(66)), '42', '&#66', 'Capital B', 1,0,0,0 ),
( char(67), ascii(char(67)), '43', '&#67', 'Capital C', 1,0,0,0 ),
( char(68), ascii(char(68)), '44', '&#68', 'Capital D', 1,0,0,0 ),
( char(69), ascii(char(69)), '45', '&#69', 'Capital E', 1,0,0,0 ),
( char(70), ascii(char(70)), '46', '&#70', 'Capital F', 1,0,0,0 ),
( char(71), ascii(char(71)), '47', '&#71', 'Capital G', 1,0,0,0 ),
( char(72), ascii(char(72)), '48', '&#72', 'Capital H', 1,0,0,0 ),
( char(73), ascii(char(73)), '49', '&#73', 'Capital I', 1,0,0,0 ),
( char(74), ascii(char(74)), '4A', '&#74', 'Capital J', 1,0,0,0 ),
( char(75), ascii(char(75)), '4B', '&#75', 'Capital K', 1,0,0,0 ),
( char(76), ascii(char(76)), '4C', '&#76', 'Capital L', 1,0,0,0 ),
( char(77), ascii(char(77)), '4D', '&#77', 'Capital M', 1,0,0,0 ),
( char(78), ascii(char(78)), '4E', '&#78', 'Capital N', 1,0,0,0 ),
( char(79), ascii(char(79)), '4F', '&#79', 'Capital O', 1,0,0,0 ),
( char(80), ascii(char(80)), '50', '&#80', 'Capital P', 1,0,0,0 ),
( char(81), ascii(char(81)), '51', '&#81', 'Capital Q', 1,0,0,0 ),
( char(82), ascii(char(82)), '52', '&#82', 'Capital R', 1,0,0,0 ),
( char(83), ascii(char(83)), '53', '&#83', 'Capital S', 1,0,0,0 ),
( char(84), ascii(char(84)), '54', '&#84', 'Capital T', 1,0,0,0 ),
( char(85), ascii(char(85)), '55', '&#85', 'Capital U', 1,0,0,0 ),
( char(86), ascii(char(86)), '56', '&#86', 'Capital V', 1,0,0,0 ),
( char(87), ascii(char(87)), '57', '&#87', 'Capital W', 1,0,0,0 ),
( char(88), ascii(char(88)), '58', '&#88', 'Capital X', 1,0,0,0 ),
( char(89), ascii(char(89)), '59', '&#89', 'Capital Y', 1,0,0,0 ),
( char(90), ascii(char(90)), '5A', '&#90', 'Capital Z', 1,0,0,0 ),
( char(91), ascii(char(91)), '5B', '&#91', 'Left square bracket', 1,0,0,0 ),
( char(92), ascii(char(92)), '5C', '&#92', 'Backslash', 1,0,0,0 ),
( char(93), ascii(char(93)), '5D', '&#93', 'Right square bracket', 1,0,0,0 ),
( char(94), ascii(char(94)), '5E', '&#94', 'Caret / circumflex', 1,0,0,0 ),
( char(95), ascii(char(95)), '5F', '&#95', 'Underscore', 1,0,0,0 ),
( char(96), ascii(char(96)), '60', '&#96', 'Grave accent', 1,0,0,0 ),
( char(97), ascii(char(97)), '61', '&#97', 'Small a', 1,0,0,0 ),
( char(98), ascii(char(98)), '62', '&#98', 'Small b', 1,0,0,0 ),
( char(99), ascii(char(99)), '63', '&#99', 'Small c', 1,0,0,0 ),
( char(100), ascii(char(100)), '64', '&#100', 'Small d', 1,0,0,0 ),
( char(101), ascii(char(101)), '65', '&#101', 'Small e', 1,0,0,0 ),
( char(102), ascii(char(102)), '66', '&#102', 'Small f', 1,0,0,0 ),
( char(103), ascii(char(103)), '67', '&#103', 'Small g', 1,0,0,0 ),
( char(104), ascii(char(104)), '68', '&#104', 'Small h', 1,0,0,0 ),
( char(105), ascii(char(105)), '69', '&#105', 'Small i', 1,0,0,0 ),
( char(106), ascii(char(106)), '6A', '&#106', 'Small j', 1,0,0,0 ),
( char(107), ascii(char(107)), '6B', '&#107', 'Small k', 1,0,0,0 ),
( char(108), ascii(char(108)), '6C', '&#108', 'Small l', 1,0,0,0 ),
( char(109), ascii(char(109)), '6D', '&#109', 'Small m', 1,0,0,0 ),
( char(110), ascii(char(110)), '6E', '&#110', 'Small n', 1,0,0,0 ),
( char(111), ascii(char(111)), '6F', '&#111', 'Small o', 1,0,0,0 ),
( char(112), ascii(char(112)), '70', '&#112', 'Small p', 1,0,0,0 ),
( char(113), ascii(char(113)), '71', '&#113', 'Small q', 1,0,0,0 ),
( char(114), ascii(char(114)), '72', '&#114', 'Small r', 1,0,0,0 ),
( char(115), ascii(char(115)), '73', '&#115', 'Small s', 1,0,0,0 ),
( char(116), ascii(char(116)), '74', '&#116', 'Small t', 1,0,0,0 ),
( char(117), ascii(char(117)), '75', '&#117', 'Small u', 1,0,0,0 ),
( char(118), ascii(char(118)), '76', '&#118', 'Small v', 1,0,0,0 ),
( char(119), ascii(char(119)), '77', '&#119', 'Small w', 1,0,0,0 ),
( char(120), ascii(char(120)), '78', '&#120', 'Small x', 1,0,0,0 ),
( char(121), ascii(char(121)), '79', '&#121', 'Small y', 1,0,0,0 ),
( char(122), ascii(char(122)), '7A', '&#122', 'Small z', 1,0,0,0 ),
( char(123), ascii(char(123)), '7B', '&#123', 'Left curly bracket', 1,0,0,0 ),
( char(124), ascii(char(124)), '7C', '&#124', 'Vertical bar', 1,0,0,0 ),
( char(125), ascii(char(125)), '7D', '&#125', 'Right curly bracket', 1,0,0,0 ),
( char(126), ascii(char(126)), '7E', '&#126', 'Tilde', 1,0,0,0 ),
( char(127), ascii(char(127)), '7F', '&#127', 'Delete', 0,1,0,0 ),
( char(128), ascii(char(128)), '80', '&#128', null, 1,0,0,0 ),
( char(129), ascii(char(129)), '81', '&#129', null, 1,0,0,0 ),
( char(130), ascii(char(130)), '82', '&#130', null, 1,0,0,0 ),
( char(131), ascii(char(131)), '83', '&#131', null, 1,0,0,0 ),
( char(132), ascii(char(132)), '84', '&#132', null, 1,0,0,0 ),
( char(133), ascii(char(133)), '85', '&#133', null, 1,0,0,0 ),
( char(134), ascii(char(134)), '86', '&#134', null, 1,0,0,0 ),
( char(135), ascii(char(135)), '87', '&#135', null, 1,0,0,0 ),
( char(136), ascii(char(136)), '88', '&#136', null, 1,0,0,0 ),
( char(137), ascii(char(137)), '89', '&#137', null, 1,0,0,0 ),
( char(138), ascii(char(138)), '8A', '&#138', null, 1,0,0,0 ),
( char(139), ascii(char(139)), '8B', '&#139', null, 1,0,0,0 ),
( char(140), ascii(char(140)), '8C', '&#140', null, 1,0,0,0 ),
( char(141), ascii(char(141)), '8D', '&#141', null, 1,0,0,0 ),
( char(142), ascii(char(142)), '8E', '&#142', null, 1,0,0,0 ),
( char(143), ascii(char(143)), '8F', '&#143', null, 1,0,0,0 ),
( char(144), ascii(char(144)), '90', '&#144', null, 1,0,0,0 ),
( char(145), ascii(char(145)), '91', '&#145', null, 1,0,0,0 ),
( char(146), ascii(char(146)), '92', '&#146', null, 1,0,0,0 ),
( char(147), ascii(char(147)), '93', '&#147', null, 1,0,0,0 ),
( char(148), ascii(char(148)), '94', '&#148', null, 1,0,0,0 ),
( char(149), ascii(char(149)), '95', '&#149', null, 1,0,0,0 ),
( char(150), ascii(char(150)), '96', '&#150', null, 1,0,0,0 ),
( char(151), ascii(char(151)), '97', '&#151', null, 1,0,0,0 ),
( char(152), ascii(char(152)), '98', '&#152', null, 1,0,0,0 ),
( char(153), ascii(char(153)), '99', '&#153', null, 1,0,0,0 ),
( char(154), ascii(char(154)), '9A', '&#154', null, 1,0,0,0 ),
( char(155), ascii(char(155)), '9B', '&#155', null, 1,0,0,0 ),
( char(156), ascii(char(156)), '9C', '&#156', null, 1,0,0,0 ),
( char(157), ascii(char(157)), '9D', '&#157', null, 1,0,0,0 ),
( char(158), ascii(char(158)), '9E', '&#158', null, 1,0,0,0 ),
( char(159), ascii(char(159)), '9F', '&#159', null, 1,0,0,0 ),
( char(160), ascii(char(160)), 'A0', '&#160', null, 1,0,0,0 ),
( char(161), ascii(char(161)), 'A1', '&#161', null, 1,0,0,0 ),
( char(162), ascii(char(162)), 'A2', '&#162', null, 1,0,0,0 ),
( char(163), ascii(char(163)), 'A3', '&#163', null, 1,0,0,0 ),
( char(164), ascii(char(164)), 'A4', '&#164', null, 1,0,0,0 ),
( char(165), ascii(char(165)), 'A5', '&#165', null, 1,0,0,0 ),
( char(166), ascii(char(166)), 'A6', '&#166', null, 1,0,0,0 ),
( char(167), ascii(char(167)), 'A7', '&#167', null, 1,0,0,0 ),
( char(168), ascii(char(168)), 'A8', '&#168', null, 1,0,0,0 ),
( char(169), ascii(char(169)), 'A9', '&#169', null, 1,0,0,0 ),
( char(170), ascii(char(170)), 'AA', '&#170', null, 1,0,0,0 ),
( char(171), ascii(char(171)), 'AB', '&#171', null, 1,0,0,0 ),
( char(172), ascii(char(172)), 'AC', '&#172', null, 1,0,0,0 ),
( char(173), ascii(char(173)), 'AD', '&#173', null, 1,0,0,0 ),
( char(174), ascii(char(174)), 'AE', '&#174', null, 1,0,0,0 ),
( char(175), ascii(char(175)), 'AF', '&#175', null, 1,0,0,0 ),
( char(176), ascii(char(176)), 'B0', '&#176', null, 1,0,0,0 ),
( char(177), ascii(char(177)), 'B1', '&#177', null, 1,0,0,0 ),
( char(178), ascii(char(178)), 'B2', '&#178', null, 1,0,0,0 ),
( char(179), ascii(char(179)), 'B3', '&#179', null, 1,0,0,0 ),
( char(180), ascii(char(180)), 'B4', '&#180', null, 1,0,0,0 ),
( char(181), ascii(char(181)), 'B5', '&#181', null, 1,0,0,0 ),
( char(182), ascii(char(182)), 'B6', '&#182', null, 1,0,0,0 ),
( char(183), ascii(char(183)), 'B7', '&#183', null, 1,0,0,0 ),
( char(184), ascii(char(184)), 'B8', '&#184', null, 1,0,0,0 ),
( char(185), ascii(char(185)), 'B9', '&#185', null, 1,0,0,0 ),
( char(186), ascii(char(186)), 'BA', '&#186', null, 1,0,0,0 ),
( char(187), ascii(char(187)), 'BB', '&#187', null, 1,0,0,0 ),
( char(188), ascii(char(188)), 'BC', '&#188', null, 1,0,0,0 ),
( char(189), ascii(char(189)), 'BD', '&#189', null, 1,0,0,0 ),
( char(190), ascii(char(190)), 'BE', '&#190', null, 1,0,0,0 ),
( char(191), ascii(char(191)), 'BF', '&#191', null, 1,0,0,0 ),
( char(192), ascii(char(192)), 'C0', '&#192', null, 1,0,0,0 ),
( char(193), ascii(char(193)), 'C1', '&#193', null, 1,0,0,0 ),
( char(194), ascii(char(194)), 'C2', '&#194', null, 1,0,0,0 ),
( char(195), ascii(char(195)), 'C3', '&#195', null, 1,0,0,0 ),
( char(196), ascii(char(196)), 'C4', '&#196', null, 1,0,0,0 ),
( char(197), ascii(char(197)), 'C5', '&#197', null, 1,0,0,0 ),
( char(198), ascii(char(198)), 'C6', '&#198', null, 1,0,0,0 ),
( char(199), ascii(char(199)), 'C7', '&#199', null, 1,0,0,0 ),
( char(200), ascii(char(200)), 'C8', '&#200', null, 1,0,0,0 ),
( char(201), ascii(char(201)), 'C9', '&#201', null, 1,0,0,0 ),
( char(202), ascii(char(202)), 'CA', '&#202', null, 1,0,0,0 ),
( char(203), ascii(char(203)), 'CB', '&#203', null, 1,0,0,0 ),
( char(204), ascii(char(204)), 'CC', '&#204', null, 1,0,0,0 ),
( char(205), ascii(char(205)), 'CD', '&#205', null, 1,0,0,0 ),
( char(206), ascii(char(206)), 'CE', '&#206', null, 1,0,0,0 ),
( char(207), ascii(char(207)), 'CF', '&#207', null, 1,0,0,0 ),
( char(208), ascii(char(208)), 'D0', '&#208', null, 1,0,0,0 ),
( char(209), ascii(char(209)), 'D1', '&#209', null, 1,0,0,0 ),
( char(210), ascii(char(210)), 'D2', '&#210', null, 1,0,0,0 ),
( char(211), ascii(char(211)), 'D3', '&#211', null, 1,0,0,0 ),
( char(212), ascii(char(212)), 'D4', '&#212', null, 1,0,0,0 ),
( char(213), ascii(char(213)), 'D5', '&#213', null, 1,0,0,0 ),
( char(214), ascii(char(214)), 'D6', '&#214', null, 1,0,0,0 ),
( char(215), ascii(char(215)), 'D7', '&#215', null, 1,0,0,0 ),
( char(216), ascii(char(216)), 'D8', '&#216', null, 1,0,0,0 ),
( char(217), ascii(char(217)), 'D9', '&#217', null, 1,0,0,0 ),
( char(218), ascii(char(218)), 'DA', '&#218', null, 1,0,0,0 ),
( char(219), ascii(char(219)), 'DB', '&#219', null, 1,0,0,0 ),
( char(220), ascii(char(220)), 'DC', '&#220', null, 1,0,0,0 ),
( char(221), ascii(char(221)), 'DD', '&#221', null, 1,0,0,0 ),
( char(222), ascii(char(222)), 'DE', '&#222', null, 1,0,0,0 ),
( char(223), ascii(char(223)), 'DF', '&#223', null, 1,0,0,0 ),
( char(224), ascii(char(224)), 'E0', '&#224', null, 1,0,0,0 ),
( char(225), ascii(char(225)), 'E1', '&#225', null, 1,0,0,0 ),
( char(226), ascii(char(226)), 'E2', '&#226', null, 1,0,0,0 ),
( char(227), ascii(char(227)), 'E3', '&#227', null, 1,0,0,0 ),
( char(228), ascii(char(228)), 'E4', '&#228', null, 1,0,0,0 ),
( char(229), ascii(char(229)), 'E5', '&#229', null, 1,0,0,0 ),
( char(230), ascii(char(230)), 'E6', '&#230', null, 1,0,0,0 ),
( char(231), ascii(char(231)), 'E7', '&#231', null, 1,0,0,0 ),
( char(232), ascii(char(232)), 'E8', '&#232', null, 1,0,0,0 ),
( char(233), ascii(char(233)), 'E9', '&#233', null, 1,0,0,0 ),
( char(234), ascii(char(234)), 'EA', '&#234', null, 1,0,0,0 ),
( char(235), ascii(char(235)), 'EB', '&#235', null, 1,0,0,0 ),
( char(236), ascii(char(236)), 'EC', '&#236', null, 1,0,0,0 ),
( char(237), ascii(char(237)), 'ED', '&#237', null, 1,0,0,0 ),
( char(238), ascii(char(238)), 'EE', '&#238', null, 1,0,0,0 ),
( char(239), ascii(char(239)), 'EF', '&#239', null, 1,0,0,0 ),
( char(240), ascii(char(240)), 'F0', '&#240', null, 1,0,0,0 ),
( char(241), ascii(char(241)), 'F1', '&#241', null, 1,0,0,0 ),
( char(242), ascii(char(242)), 'F2', '&#242', null, 1,0,0,0 ),
( char(243), ascii(char(243)), 'F3', '&#243', null, 1,0,0,0 ),
( char(244), ascii(char(244)), 'F4', '&#244', null, 1,0,0,0 ),
( char(245), ascii(char(245)), 'F5', '&#245', null, 1,0,0,0 ),
( char(246), ascii(char(246)), 'F6', '&#246', null, 1,0,0,0 ),
( char(247), ascii(char(247)), 'F7', '&#247', null, 1,0,0,0 ),
( char(248), ascii(char(248)), 'F8', '&#248', null, 1,0,0,0 ),
( char(249), ascii(char(249)), 'F9', '&#249', null, 1,0,0,0 ),
( char(250), ascii(char(250)), 'FA', '&#250', null, 1,0,0,0 ),
( char(251), ascii(char(251)), 'FB', '&#251', null, 1,0,0,0 ),
( char(252), ascii(char(252)), 'FC', '&#252', null, 1,0,0,0 ),
( char(253), ascii(char(253)), 'FD', '&#253', null, 1,0,0,0 ),
( char(254), ascii(char(254)), 'FE', '&#254', null, 1,0,0,0 ),
( char(255), ascii(char(255)), 'FF', '&#255', null, 1,0,0,0 )

update Ascii_MO set isNumber = 1 where id between 48 and 57;
update Ascii_MO set isAlpha = 1
where
asciiDecimal between 65 and 90
or asciiDecimal between 97 and 122
or asciiDecimal in ( 154, 156, 158, 159, 215, 247 )
or asciiDecimal between 192 and 214
or asciiDecimal between 216 and 221
or asciiDecimal between 222 and 246
or asciiDecimal between 248 and 255;
go

create or alter procedure dbo.usp_MyProcedure_MO
@string varchar(max)
with execute as owner, native_compilation, schemabinding
as
begin atomic with ( transaction isolation level = snapshot, language = N'us_english' )

declare @tally dbo.Type_Tally_MO;
declare @id int = 1;

while ( @id <= len(@string) )
begin
insert @tally ( id, charValue )
values ( @id, substring(@string,@id,1) )

set @id += 1;
end;

select a.asciiChar, a.asciiDecimal, a.asciiDescription
from
@tally t
inner join dbo.Ascii_MO a on
a.asciiChar = convert(char(1),t.charValue)

select id = min(t.id)
from
@tally t
inner join dbo.Ascii_MO a on
a.asciiChar = convert(char(1),t.charValue)
and a.isNonPrintable = 1

end;
go

declare @a varchar(10) = 'aa'
exec dbo.usp_MyProcedure_MO @a;