解决方法
对于模型的粗鲁性我很抱歉,但是这似乎是正确的CRC32计算.
我不是一个Tsql专家,我确信这可以通过一个真正的sql Server pro来强大的改进…
我不是一个Tsql专家,我确信这可以通过一个真正的sql Server pro来强大的改进…
@input是计算CRC32的变量.
将其打包为sproc或udf应该是微不足道的,查找表可以被分解为永久表(或甚至是即时计算).
无论如何,它似乎工作.我有兴趣看到任何改进,因为总是很好的学习新的技巧:)
编辑:我已经检查了我的结果与http://crc32-checksum.waraxe.us/,似乎很好,迄今为止.
安德鲁
DECLARE @input VARCHAR(50) SET @input = 'test' SET NOCOUNT ON DECLARE @tblLookup TABLE (ID INT IDENTITY(0,1) NOT NULL,Value BIGINT) INSERT INTO @tblLookup VALUES (0) INSERT INTO @tblLookup VALUES (1996959894) INSERT INTO @tblLookup VALUES (3993919788) INSERT INTO @tblLookup VALUES (2567524794) INSERT INTO @tblLookup VALUES (124634137) INSERT INTO @tblLookup VALUES (1886057615) INSERT INTO @tblLookup VALUES (3915621685) INSERT INTO @tblLookup VALUES (2657392035) INSERT INTO @tblLookup VALUES (249268274) INSERT INTO @tblLookup VALUES (2044508324) INSERT INTO @tblLookup VALUES (3772115230) INSERT INTO @tblLookup VALUES (2547177864) INSERT INTO @tblLookup VALUES (162941995) INSERT INTO @tblLookup VALUES (2125561021) INSERT INTO @tblLookup VALUES (3887607047) INSERT INTO @tblLookup VALUES (2428444049) INSERT INTO @tblLookup VALUES (498536548) INSERT INTO @tblLookup VALUES (1789927666) INSERT INTO @tblLookup VALUES (4089016648) INSERT INTO @tblLookup VALUES (2227061214) INSERT INTO @tblLookup VALUES (450548861) INSERT INTO @tblLookup VALUES (1843258603) INSERT INTO @tblLookup VALUES (4107580753) INSERT INTO @tblLookup VALUES (2211677639) INSERT INTO @tblLookup VALUES (325883990) INSERT INTO @tblLookup VALUES (1684777152) INSERT INTO @tblLookup VALUES (4251122042) INSERT INTO @tblLookup VALUES (2321926636) INSERT INTO @tblLookup VALUES (335633487) INSERT INTO @tblLookup VALUES (1661365465) INSERT INTO @tblLookup VALUES (4195302755) INSERT INTO @tblLookup VALUES (2366115317) INSERT INTO @tblLookup VALUES (997073096) INSERT INTO @tblLookup VALUES (1281953886) INSERT INTO @tblLookup VALUES (3579855332) INSERT INTO @tblLookup VALUES (2724688242) INSERT INTO @tblLookup VALUES (1006888145) INSERT INTO @tblLookup VALUES (1258607687) INSERT INTO @tblLookup VALUES (3524101629) INSERT INTO @tblLookup VALUES (2768942443) INSERT INTO @tblLookup VALUES (901097722) INSERT INTO @tblLookup VALUES (1119000684) INSERT INTO @tblLookup VALUES (3686517206) INSERT INTO @tblLookup VALUES (2898065728) INSERT INTO @tblLookup VALUES (853044451) INSERT INTO @tblLookup VALUES (1172266101) INSERT INTO @tblLookup VALUES (3705015759) INSERT INTO @tblLookup VALUES (2882616665) INSERT INTO @tblLookup VALUES (651767980) INSERT INTO @tblLookup VALUES (1373503546) INSERT INTO @tblLookup VALUES (3369554304) INSERT INTO @tblLookup VALUES (3218104598) INSERT INTO @tblLookup VALUES (565507253) INSERT INTO @tblLookup VALUES (1454621731) INSERT INTO @tblLookup VALUES (3485111705) INSERT INTO @tblLookup VALUES (3099436303) INSERT INTO @tblLookup VALUES (671266974) INSERT INTO @tblLookup VALUES (1594198024) INSERT INTO @tblLookup VALUES (3322730930) INSERT INTO @tblLookup VALUES (2970347812) INSERT INTO @tblLookup VALUES (795835527) INSERT INTO @tblLookup VALUES (1483230225) INSERT INTO @tblLookup VALUES (3244367275) INSERT INTO @tblLookup VALUES (3060149565) INSERT INTO @tblLookup VALUES (1994146192) INSERT INTO @tblLookup VALUES (31158534) INSERT INTO @tblLookup VALUES (2563907772) INSERT INTO @tblLookup VALUES (4023717930) INSERT INTO @tblLookup VALUES (1907459465) INSERT INTO @tblLookup VALUES (112637215) INSERT INTO @tblLookup VALUES (2680153253) INSERT INTO @tblLookup VALUES (3904427059) INSERT INTO @tblLookup VALUES (2013776290) INSERT INTO @tblLookup VALUES (251722036) INSERT INTO @tblLookup VALUES (2517215374) INSERT INTO @tblLookup VALUES (3775830040) INSERT INTO @tblLookup VALUES (2137656763) INSERT INTO @tblLookup VALUES (141376813) INSERT INTO @tblLookup VALUES (2439277719) INSERT INTO @tblLookup VALUES (3865271297) INSERT INTO @tblLookup VALUES (1802195444) INSERT INTO @tblLookup VALUES (476864866) INSERT INTO @tblLookup VALUES (2238001368) INSERT INTO @tblLookup VALUES (4066508878) INSERT INTO @tblLookup VALUES (1812370925) INSERT INTO @tblLookup VALUES (453092731) INSERT INTO @tblLookup VALUES (2181625025) INSERT INTO @tblLookup VALUES (4111451223) INSERT INTO @tblLookup VALUES (1706088902) INSERT INTO @tblLookup VALUES (314042704) INSERT INTO @tblLookup VALUES (2344532202) INSERT INTO @tblLookup VALUES (4240017532) INSERT INTO @tblLookup VALUES (1658658271) INSERT INTO @tblLookup VALUES (366619977) INSERT INTO @tblLookup VALUES (2362670323) INSERT INTO @tblLookup VALUES (4224994405) INSERT INTO @tblLookup VALUES (1303535960) INSERT INTO @tblLookup VALUES (984961486) INSERT INTO @tblLookup VALUES (2747007092) INSERT INTO @tblLookup VALUES (3569037538) INSERT INTO @tblLookup VALUES (1256170817) INSERT INTO @tblLookup VALUES (1037604311) INSERT INTO @tblLookup VALUES (2765210733) INSERT INTO @tblLookup VALUES (3554079995) INSERT INTO @tblLookup VALUES (1131014506) INSERT INTO @tblLookup VALUES (879679996) INSERT INTO @tblLookup VALUES (2909243462) INSERT INTO @tblLookup VALUES (3663771856) INSERT INTO @tblLookup VALUES (1141124467) INSERT INTO @tblLookup VALUES (855842277) INSERT INTO @tblLookup VALUES (2852801631) INSERT INTO @tblLookup VALUES (3708648649) INSERT INTO @tblLookup VALUES (1342533948) INSERT INTO @tblLookup VALUES (654459306) INSERT INTO @tblLookup VALUES (3188396048) INSERT INTO @tblLookup VALUES (3373015174) INSERT INTO @tblLookup VALUES (1466479909) INSERT INTO @tblLookup VALUES (544179635) INSERT INTO @tblLookup VALUES (3110523913) INSERT INTO @tblLookup VALUES (3462522015) INSERT INTO @tblLookup VALUES (1591671054) INSERT INTO @tblLookup VALUES (702138776) INSERT INTO @tblLookup VALUES (2966460450) INSERT INTO @tblLookup VALUES (3352799412) INSERT INTO @tblLookup VALUES (1504918807) INSERT INTO @tblLookup VALUES (783551873) INSERT INTO @tblLookup VALUES (3082640443) INSERT INTO @tblLookup VALUES (3233442989) INSERT INTO @tblLookup VALUES (3988292384) INSERT INTO @tblLookup VALUES (2596254646) INSERT INTO @tblLookup VALUES (62317068) INSERT INTO @tblLookup VALUES (1957810842) INSERT INTO @tblLookup VALUES (3939845945) INSERT INTO @tblLookup VALUES (2647816111) INSERT INTO @tblLookup VALUES (81470997) INSERT INTO @tblLookup VALUES (1943803523) INSERT INTO @tblLookup VALUES (3814918930) INSERT INTO @tblLookup VALUES (2489596804) INSERT INTO @tblLookup VALUES (225274430) INSERT INTO @tblLookup VALUES (2053790376) INSERT INTO @tblLookup VALUES (3826175755) INSERT INTO @tblLookup VALUES (2466906013) INSERT INTO @tblLookup VALUES (167816743) INSERT INTO @tblLookup VALUES (2097651377) INSERT INTO @tblLookup VALUES (4027552580) INSERT INTO @tblLookup VALUES (2265490386) INSERT INTO @tblLookup VALUES (503444072) INSERT INTO @tblLookup VALUES (1762050814) INSERT INTO @tblLookup VALUES (4150417245) INSERT INTO @tblLookup VALUES (2154129355) INSERT INTO @tblLookup VALUES (426522225) INSERT INTO @tblLookup VALUES (1852507879) INSERT INTO @tblLookup VALUES (4275313526) INSERT INTO @tblLookup VALUES (2312317920) INSERT INTO @tblLookup VALUES (282753626) INSERT INTO @tblLookup VALUES (1742555852) INSERT INTO @tblLookup VALUES (4189708143) INSERT INTO @tblLookup VALUES (2394877945) INSERT INTO @tblLookup VALUES (397917763) INSERT INTO @tblLookup VALUES (1622183637) INSERT INTO @tblLookup VALUES (3604390888) INSERT INTO @tblLookup VALUES (2714866558) INSERT INTO @tblLookup VALUES (953729732) INSERT INTO @tblLookup VALUES (1340076626) INSERT INTO @tblLookup VALUES (3518719985) INSERT INTO @tblLookup VALUES (2797360999) INSERT INTO @tblLookup VALUES (1068828381) INSERT INTO @tblLookup VALUES (1219638859) INSERT INTO @tblLookup VALUES (3624741850) INSERT INTO @tblLookup VALUES (2936675148) INSERT INTO @tblLookup VALUES (906185462) INSERT INTO @tblLookup VALUES (1090812512) INSERT INTO @tblLookup VALUES (3747672003) INSERT INTO @tblLookup VALUES (2825379669) INSERT INTO @tblLookup VALUES (829329135) INSERT INTO @tblLookup VALUES (1181335161) INSERT INTO @tblLookup VALUES (3412177804) INSERT INTO @tblLookup VALUES (3160834842) INSERT INTO @tblLookup VALUES (628085408) INSERT INTO @tblLookup VALUES (1382605366) INSERT INTO @tblLookup VALUES (3423369109) INSERT INTO @tblLookup VALUES (3138078467) INSERT INTO @tblLookup VALUES (570562233) INSERT INTO @tblLookup VALUES (1426400815) INSERT INTO @tblLookup VALUES (3317316542) INSERT INTO @tblLookup VALUES (2998733608) INSERT INTO @tblLookup VALUES (733239954) INSERT INTO @tblLookup VALUES (1555261956) INSERT INTO @tblLookup VALUES (3268935591) INSERT INTO @tblLookup VALUES (3050360625) INSERT INTO @tblLookup VALUES (752459403) INSERT INTO @tblLookup VALUES (1541320221) INSERT INTO @tblLookup VALUES (2607071920) INSERT INTO @tblLookup VALUES (3965973030) INSERT INTO @tblLookup VALUES (1969922972) INSERT INTO @tblLookup VALUES (40735498) INSERT INTO @tblLookup VALUES (2617837225) INSERT INTO @tblLookup VALUES (3943577151) INSERT INTO @tblLookup VALUES (1913087877) INSERT INTO @tblLookup VALUES (83908371) INSERT INTO @tblLookup VALUES (2512341634) INSERT INTO @tblLookup VALUES (3803740692) INSERT INTO @tblLookup VALUES (2075208622) INSERT INTO @tblLookup VALUES (213261112) INSERT INTO @tblLookup VALUES (2463272603) INSERT INTO @tblLookup VALUES (3855990285) INSERT INTO @tblLookup VALUES (2094854071) INSERT INTO @tblLookup VALUES (198958881) INSERT INTO @tblLookup VALUES (2262029012) INSERT INTO @tblLookup VALUES (4057260610) INSERT INTO @tblLookup VALUES (1759359992) INSERT INTO @tblLookup VALUES (534414190) INSERT INTO @tblLookup VALUES (2176718541) INSERT INTO @tblLookup VALUES (4139329115) INSERT INTO @tblLookup VALUES (1873836001) INSERT INTO @tblLookup VALUES (414664567) INSERT INTO @tblLookup VALUES (2282248934) INSERT INTO @tblLookup VALUES (4279200368) INSERT INTO @tblLookup VALUES (1711684554) INSERT INTO @tblLookup VALUES (285281116) INSERT INTO @tblLookup VALUES (2405801727) INSERT INTO @tblLookup VALUES (4167216745) INSERT INTO @tblLookup VALUES (1634467795) INSERT INTO @tblLookup VALUES (376229701) INSERT INTO @tblLookup VALUES (2685067896) INSERT INTO @tblLookup VALUES (3608007406) INSERT INTO @tblLookup VALUES (1308918612) INSERT INTO @tblLookup VALUES (956543938) INSERT INTO @tblLookup VALUES (2808555105) INSERT INTO @tblLookup VALUES (3495958263) INSERT INTO @tblLookup VALUES (1231636301) INSERT INTO @tblLookup VALUES (1047427035) INSERT INTO @tblLookup VALUES (2932959818) INSERT INTO @tblLookup VALUES (3654703836) INSERT INTO @tblLookup VALUES (1088359270) INSERT INTO @tblLookup VALUES (936918000) INSERT INTO @tblLookup VALUES (2847714899) INSERT INTO @tblLookup VALUES (3736837829) INSERT INTO @tblLookup VALUES (1202900863) INSERT INTO @tblLookup VALUES (817233897) INSERT INTO @tblLookup VALUES (3183342108) INSERT INTO @tblLookup VALUES (3401237130) INSERT INTO @tblLookup VALUES (1404277552) INSERT INTO @tblLookup VALUES (615818150) INSERT INTO @tblLookup VALUES (3134207493) INSERT INTO @tblLookup VALUES (3453421203) INSERT INTO @tblLookup VALUES (1423857449) INSERT INTO @tblLookup VALUES (601450431) INSERT INTO @tblLookup VALUES (3009837614) INSERT INTO @tblLookup VALUES (3294710456) INSERT INTO @tblLookup VALUES (1567103746) INSERT INTO @tblLookup VALUES (711928724) INSERT INTO @tblLookup VALUES (3020668471) INSERT INTO @tblLookup VALUES (3272380065) INSERT INTO @tblLookup VALUES (1510334235) INSERT INTO @tblLookup VALUES (755167117) DECLARE @crc BIGINT,@len INT,@i INT,@index INT DECLARE @tblval BIGINT SET @crc = 0xFFFFFFFF SET @len = LEN(@input) SET @i = 1 WHILE @i <= @len BEGIN SET @index = ((@crc & 0xff) ^ ASCII(SUBSTRING(@input,@i,1))) SET @tblval = (SELECT Value FROM @tblLookup WHERE ID = @Index) SET @crc = (@crc / 256) ^ @tblval SET @i = @i + 1 END SET @crc = ~@crc SELECT @crc as CRC32,CONVERT(VARBINARY(4),@crc) as CRC32Hex