How to create a Function that calculates CRC16 in SQL Server?

Asked

Viewed 259 times

0

can tell me if there is any sql function that calculates CRC16, the calculation method is listed as "CRC-CCITT (0xFFFF)".

CRC-CCITT (0xFFFF) -> polynomial: x 16 + x 12 + x 5 + 1

I tried to create the function below, but it is not calculating in the right way:

(
    @input varchar(max)
)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @crc        bigint = 0xffff
    ,       @result     int
    ;
    SELECT @crc = dbo.CRC16calc(@crc, Ascii(Substring(@input, v.id, 1)))
    FROM    dbo.IndexTable(1, LEN(@input)) AS v
    ORDER
    BY      v.Id
    ;
    SET @result = (CONVERT(int, CONVERT(VARBINARY(4), ~@crc))) ;
    RETURN @result ;
END
sql database sql-server function checksum

  • Just to confirm the expected result ... if you enter the string "123456789", the expected result is the integer number 10673 ? ( i.e., "0x29B1" in hexadecimal ) ?

  • I don’t know Gabriel, that’s an opinion but, it wouldn’t be easier to do it in a language in . net, which should many examples and code ready, install the dll on the server and import the Function? code sql was not done well for these things, it is like wanting to program a high performance game in cobol, That’s not what the language was made for. I’ve had code like this with encryption algorithms in older versions of sql, and it worked well a code in . net imported as a Function

1 answer

2


I found exactly the function you are looking for, but the source is in C -- You need to "just" convert to MSSQL functions ... I hope it will be useful to you.

https://stackoverflow.com/a/23726131/4877541

There are other ways to do this ... if feasible, you can create a DLL with the CRC function, and make the Bind to call the DLL function from within an SQL statement and/or a Stored Procedure, more details on the link below:

https://dbtut.com/index.php/2019/05/05/what-is-clr-and-how-to-import-dll-in-sql-server/

Browser other questions tagged sql database sql-server function checksum

You are not signed in. Login or sign up in order to post.