CRC-16 SQL CCTI

Question

I have Sql Code that converts input to CRC-32 Need help to convert CRC-32 to CRC-16 is there any other resource available. below is my SQl Please help I need 4 digit output code

DECLARE @input VARCHAR(50)
SET @input = '0002020102110202000424PK79013914031004'

SET NOCOUNT ON
DECLARE
    @crc bigint = 0xFFFF,
    @Lookup varbinary(2048) = '0x00000x10210x20420x30630x40840x50a50x60c60x70e70x81080x91290xa14a0xb16b0xc18c0xd1ad0xe1ce0xf1ef0x12310x02100x32730x22520x52b50x42940x72f70x62d60x93390x83180xb37b0xa35a0xd3bd0xc39c0xf3ff0xe3de0x24620x34430x04200x14010x64e60x74c70x44a40x5485
0xa56a0xb54b0x85280x95090xe5ee0xf5cf0xc5ac0xd58d
0x36530x26720x16110x06300x76d70x66f60x56950x46b4
0xb75b0xa77a0x97190x87380xf7df0xe7fe0xd79d0xc7bc
0x48c40x58e50x68860x78a70x08400x18610x28020x3823
0xc9cc0xd9ed0xe98e0xf9af0x89480x99690xa90a0xb92b
0x5af50x4ad40x7ab70x6a960x1a710x0a500x3a330x2a12
0xdbfd0xcbdc0xfbbf0xeb9e0x9b790x8b580xbb3b0xab1a
0x6ca60x7c870x4ce40x5cc50x2c220x3c030x0c600x1c41
0xedae0xfd8f0xcdec0xddcd0xad2a0xbd0b0x8d680x9d49
0x7e970x6eb60x5ed50x4ef40x3e130x2e320x1e510x0e70
0xff9f0xefbe0xdfdd0xcffc0xbf1b0xaf3a0x9f590x8f78
0x91880x81a90xb1ca0xa1eb0xd10c0xc12d0xf14e0xe16f
0x10800x00a10x30c20x20e30x50040x40250x70460x6067
0x83b90x93980xa3fb0xb3da0xc33d0xd31c0xe37f0xf35e
0x02b10x12900x22f30x32d20x42350x52140x62770x7256
0xb5ea0xa5cb0x95a80x85890xf56e0xe54f0xd52c0xc50d
0x34e20x24c30x14a00x04810x74660x64470x54240x4405
0xa7db0xb7fa0x87990x97b80xe75f0xf77e0xc71d0xd73c
0x26d30x36f20x06910x16b00x66570x76760x46150x5634
0xd94c0xc96d0xf90e0xe92f0x99c80x89e90xb98a0xa9ab
0x58440x48650x78060x68270x18c00x08e10x38820x28a3
0xcb7d0xdb5c0xeb3f0xfb1e0x8bf90x9bd80xabbb0xbb9a
0x4a750x5a540x6a370x7a160x0af10x1ad00x2ab30x3a92
0xfd2e0xed0f0xdd6c0xcd4d0xbdaa0xad8b0x9de80x8dc9
0x7c260x6c070x5c640x4c450x3ca20x2c830x1ce00x0cc1
0xef1f0xff3e0xcf5d0xdf7c0xaf9b0xbfba0x8fd90x9ff8
0x6e170x7e360x4e550x5e740x2e930x3eb20x0ed10x1ef0'

SELECT @crc = (@crc / 256) ^ Substring(@Lookup, ((@crc & 0xFF) ^ Ascii(Substring(@input, V.Number, 1))) * 4 + 1, 4)
FROM master.dbo.spt_values V
WHERE V.type = 'P' AND V.number BETWEEN 1 AND Len(@input)

SET @crc = ~@crc;
SELECT @crc CRC32, Convert(VARBINARY(4), @crc) CRC32Hex;

Tried in sql but did not work

Well, I guess, which version of CRC-16-CCITT are you actually looking for there because you seem to be using the `0xFFFF` initialization vector instead of `0x1D0F`. I wouldn't recommend doing this in SQL Server because you can't guarantee the order of checksum calculations when trying to do this in a set-based fashion, which means that to get a correct result each time you'll need to use RBAR (Row-By-Agonizing-Row) techniques such as a `CURSOR`. If you need to calculate a CRC-16 from SQL Server you would be much better off implementing this as an SQLCLR function. — AlwaysLearning, Mar 25 '23 at 10:00
Can you please help I haven't found any example of CRC16 in SQL Server — Asif Ali, Mar 25 '23 at 10:10
That's probably because it's not the right place to be doing it. SQL Server is not a general purpose programming environment, it's a Relational Database Management System. While it is possible to calculate CRC-16-CCITT in T-SQL (the language that SQL Server uses) it's extremely inefficient to do it this way. If you need to perform a lot of these calculations it will impact system performance. — AlwaysLearning, Mar 25 '23 at 10:12

score 0 · Answer 1 · edited Mar 25 '23 at 21:36

What follows is an implementation example of CRC-16/AUG-CCITT by way of a User-Defined Function in SQL Server.

This is purely for educational purposes, I do not recommend using it in a production environment - it will perform badly, use an SQL CLR implementation instead! It performs badly because it is a CURSOR implementation which is required to ensure that the checksum calculations are executed in the correct order.

CREATE FUNCTION dbo.CRC16_CCITT(
  @Input VARCHAR(1024)
) RETURNS BINARY(2) AS
BEGIN
  -- CRC-16/AUG-CCITT using polynomial 0x1021 and initialization vector 0x1D0F
  -- Compare results against: https://crccalc.com/
  DECLARE @Lookup BINARY(512) = -- 256 * 2-byte lookup values = 512 bytes
    0x0000102120423063408450A560C670E781089129A14AB16BC18CD1ADE1CEF1EF +
    0x123102103273225252B5429472F762D693398318B37BA35AD3BDC39CF3FFE3DE +
    0x246234430420140164E674C744A45485A56AB54B85289509E5EEF5CFC5ACD58D +
    0x365326721611063076D766F6569546B4B75BA77A97198738F7DFE7FED79DC7BC +
    0x48C458E5688678A70840186128023823C9CCD9EDE98EF9AF89489969A90AB92B +
    0x5AF54AD47AB76A961A710A503A332A12DBFDCBDCFBBFEB9E9B798B58BB3BAB1A +
    0x6CA67C874CE45CC52C223C030C601C41EDAEFD8FCDECDDCDAD2ABD0B8D689D49 +
    0x7E976EB65ED54EF43E132E321E510E70FF9FEFBEDFDDCFFCBF1BAF3A9F598F78 +
    0x918881A9B1CAA1EBD10CC12DF14EE16F108000A130C220E35004402570466067 +
    0x83B99398A3FBB3DAC33DD31CE37FF35E02B1129022F332D24235521462777256 +
    0xB5EAA5CB95A88589F56EE54FD52CC50D34E224C314A004817466644754244405 +
    0xA7DBB7FA879997B8E75FF77EC71DD73C26D336F2069116B06657767646155634 +
    0xD94CC96DF90EE92F99C889E9B98AA9AB584448657806682718C008E1388228A3 +
    0xCB7DDB5CEB3FFB1E8BF99BD8ABBBBB9A4A755A546A377A160AF11AD02AB33A92 +
    0xFD2EED0FDD6CCD4DBDAAAD8B9DE88DC97C266C075C644C453CA22C831CE00CC1 +
    0xEF1FFF3ECF5DDF7CAF9BBFBA8FD99FF86E177E364E555E742E933EB20ED11EF0;

  DECLARE @CRC INT = /*0xFFFF*/ 0x1D0F, @Number INT;
  DECLARE csrChecksumEnumerator CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
    SELECT [number]
    FROM master.dbo.spt_values
    WHERE [type] = N'P'
    AND [number] BETWEEN 1 AND LEN(@Input)
    ORDER BY [number];
  OPEN csrChecksumEnumerator;
  FETCH NEXT FROM csrChecksumEnumerator INTO @Number;
  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    SELECT @CRC = ((@CRC << 8) ^ CAST(SUBSTRING(@Lookup, ((@CRC >> 8) ^ ASCII(SUBSTRING(@Input, @Number, 1))) * 2 + 1, 2) AS INT)) & 0xFFFF;
    FETCH NEXT FROM csrChecksumEnumerator INTO @Number;
  END
  CLOSE csrChecksumEnumerator;
  DEALLOCATE csrChecksumEnumerator;
  RETURN CAST(@CRC AS BINARY(2));
END

Throwing some test vectors at it...

SELECT [Actual], [Expected], [Input]
FROM (VALUES
  (0x1D0F, ''),
  (0x9479, 'A'),
  (0xE5CC, '123456789'),
  (0x9E86, '!"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[]^_`abcdefghijklmnopqrstuvwxyz{|}~'),
  (0xE938, REPLICATE('A', 256))
) SRC ([Expected], [Input])
CROSS APPLY (SELECT dbo.CRC16_CCITT([Input]) AS [Actual]) Calculated;

Which yields the output:

Actual	Expected	Input
0x1D0F	0x1D0F
0x9479	0x9479	A
0xE5CC	0xE5CC	123456789
0x9E86	0x9E86	!"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[]^_`abcdefghijklmnopqrstuvwxyz{\|}~
0xE938	0xE938	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Using the input value from the question should result in 0x3B22:

SELECT dbo.CRC16_CCITT('0002020102110202000424PK79013914031004');

CRC-16 SQL CCTI

1 Answers1