Converts CRC16 MySQL function to SQL Server

Question

I have this function for calculating crc16, but it is in MySQL, can someone help me convert to SQL Server?

I looked in several places, but I only find the crc32, this does not work for the generation of the PIX QRcode.

Below is an example of the function I have.

CREATE DEFINER=`root`@`%` FUNCTION `CRC16`( _STRING VARCHAR(25)) RETURNS varchar(50) CHARSET utf8mb4
    DETERMINISTIC
BEGIN
    DECLARE _myCRC integer;
    DECLARE _ord INTEGER;
    DECLARE _n Integer;
    DECLARE _m Integer;
    DECLARE _strlend Integer;
    SET _myCRC := x'FFFF';

      SET _n  := 1;  
      SET _strlend := LENGTH(_STRING) ;
      
        loop_crc:  LOOP
     
            IF  _n > _strlend THEN 
                LEAVE  loop_crc;
            END  IF;
              
            SET _ord := ORD(SUBSTRING(_STRING, _n, 1) );
            SET _myCRC :=  _myCRC ^ _ord;
            SET _m := 0;     
            loop_bit:  LOOP
                IF  _m = 8 THEN 
                    LEAVE  loop_bit;
                END  IF;
                IF (_myCRC & x'0001') = x'0001' THEN
                    SET _myCRC := (_myCRC >> 1) ^ x'A001';
                ELSE
                    SET _myCRC := _myCRC >> 1;        
                END IF;
                SET  _m := _m + 1;
            END LOOP;
            SET  _n := _n + 1;

        END LOOP;

      return HEX(_myCRC); 
 END//

score 1 · Accepted Answer · answered Dec 04 '20 at 21:37

1

Converting this function to Transact-SQL should be straightforward. In general:

Get rid of the definer, the backticks and DETERMINISTIC in the function header.
For loops, use WHILE condition BEGIN ... END. Notice a 'while' condition is the negation of a 'leave' condition.
Variable and parameter names must be prefixed with @.
Use decimal literals. (Use a calculator to convert the hexadecimals.)
Use = instead of := for variable assignment.
Replace >> 1 with / 2.
Replace LENGTH with LEN.
Replace ORD with ASCII or UNICODE.
Replace HEX(...) with CONVERT(char(4), CONVERT(binary(2), ...), 2), as suggested here.

answered Dec 04 '20 at 21:37

Ruud Helderman

10,563
1
26
45

Ruud, can you tell me if there are any other changes that should be made? Below is how my code was, still, it has some inconsistencies. – Gabriel Paixão Justino May 21 '21 at 21:02
By inconsistencies, do you mean there were cases where the MySQL function would generate the correct CRC, but the SQL Server function did not? Do you have an example of input where the output was inconsistent? – Ruud Helderman May 23 '21 at 22:37

Converts CRC16 MySQL function to SQL Server

1 Answers1