Replacement for char and ascii functions in natively compiled stored procedures

  • 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;
  • 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)

  • 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.

  • 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)

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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)

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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)

  • @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)

  • 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

  • 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

  • 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;

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

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