SQLite X'...'表示列数据 [英] SQLite X'...' notation with column data

问题描述

我试图在Spiceworks中使用SQLite查询编写自定义报告。这份报告将获取硬盘序列号，不幸的是，这些硬盘序列号根据机器上的Windows和WMI版本以不同的方式存储。

三个常见示例这是足以得到实际问题）如下：

实际序列号： 5VG95AZF

带前导空格的十六进制字符串 2020202057202d44585730354341543934383433

十六进制字符串前导零：> 3030303030303030313131343330423137454342

这两个十六进制字符串更加复杂，在它们被转换成ASCII表示之后，每一对数字实际上都是向后的。这是一个例子：

$ p $ 3030303030303030313131343330423137454342 计算结果为 00000000111430B17ECB 然而，该硬盘上的实际序列号是 1141031BE7BC ，没有前导零和字节交换。根据我在本网站上阅读的其他问题和答案，这与数据的字节顺序有关。

到目前为止，我的临时查询看起来像这样（缩写为相关部分）：

  SELECT pd.model as HDModel，
 CASE 
 WHEN （X'''|| pd.serial ||''''）作为文本）
当pd.serial如202020％时，则为30303030％THEN 
 
 LTRIM（X'2020202057202d44585730354341543934383433'）
 ELSE 
 pd.serial 
 END作为HDSerial 
  

该查询的结果如下所示：

  HDModel HDSerial 
  - ---------------- ---------------------------------- --------- 
正常串行5VG95AZF 
 202020％测试用例W -DXW05CAT94843 
 303030％测试用例X'3030303030303030313131343330423137454342'
  

这表明 X'....'当给定时，符号样式转换为 W -DXW05CAT94843 的正确结果（但是向后）一个完整的文字数字（202020％线）。但是，我需要找到一种方法来对列中的实际数据执行相同的操作， pd.serial ，但我找不到方法。

我最初的想法是，如果我可以构建 X'...'表示法的字符串表示形式，那么也许 cast（）会评估它。但正如你所看到的那样，结果只是吐出 X'3030303030303030313131343330423137454342'而不是预期的 00000000111430B17ECB 。这意味着连接工作正常，但我找不到一种方法来评估它，因为十六进制和手动测试用例一样。

我一直在使用Google整天早上看看我是否缺少一些语法，但最接近的是使用 || 运算符的连接。

编辑：最终，我只是希望能够在我的查询中拥有一个简单的case语句，如下所示：

 SELECT pd.model as HDModel，
 CASE 
当pd.serial如30303030％THEN 
 LTRIM（X'pd.serial'）
当pd.serial如202020％THEN 
 LTRIM（X'pd.serial'）
 ELSE 
 pd.serial 
 END HD'
  

但是因为pd.serial被包装在单引号中，所以它被当作字符串而不是作为包含在柱。我的希望是，我只需要指定一个字符或操作符，如 X'$ pd.serial'或其他。

END编辑

如果我可以通过这个第一个障碍，我的下一个任务是尝试并移除引导零（LTRIM消耗前导空格的方式）并反转字节，但说实话，即使该部分不可行，我也会满足，因为在Excel中后处理此报告并不困难那

如果任何人都可以指出我正确的方向，我将不胜感激！如果我使用PHP或其他方法来执行此处理，显然会容易得多，但是因为我试图将它作为Spiceworks中的嵌入式报表，所以我必须在单个SQLite查询中执行此操作。

解决方案

X'...'是sqlite中的二进制表示。如果这些值是字符串，您可以直接使用它们。

这应该是一个开始：

 源码>选择X'3030303030303030313131343330423137454342'; 
 00000000111430B17ECB 
 sqlite>选择ltrim（X'3030303030303030313131343330423137454342'，'0'）; 
 111430B17ECB 
  

我希望这会让您走上正确的道路。

I am trying to write a custom report in Spiceworks, which uses SQLite queries. This report will fetch me hard drive serial numbers that are unfortunately stored in a few different ways depending on what version of Windows and WMI were on the machine.

Three common examples (which are enough to get to the actual question) are as follows:

Actual serial number: 5VG95AZF
Hexadecimal string with leading spaces: 2020202057202d44585730354341543934383433
Hexadecimal string with leading zeroes: 3030303030303030313131343330423137454342

The two hex strings are further complicated in that even after they are converted to ASCII representation, each pair of numbers are actually backwards. Here is an example:

3030303030303030313131343330423137454342 evaluates to 00000000111430B17ECB

However, the actual serial number on that hard drive is 1141031BE7BC, without leading zeroes and with the bytes swapped around. According to other questions and answers I have read on this site, this has to do with the "endianness" of the data.

My temporary query so far looks something like this (shortened to only the pertinent section):

SELECT  pd.model as HDModel,
  CASE
     WHEN pd.serial like "30303030%" THEN
         cast(('X''' || pd.serial || '''') as TEXT)
     WHEN pd.serial like "202020%" THEN
         LTRIM(X'2020202057202d44585730354341543934383433')
  ELSE
     pd.serial
  END  as HDSerial

The result of that query is something like this:

HDModel             HDSerial
-----------------   -------------------------------------------
Normal Serial       5VG95AZF
202020% test case   W -DXW05CAT94843
303030% test case   X'3030303030303030313131343330423137454342'

This shows that the X'....' notation style does convert into the correct (but backwards) result of W -DXW05CAT94843 when given a fully literal number (the 202020% line). However, I need to find a way to do the same thing to the actual data in the column, pd.serial, and I can't find a way.

My initial thought was that if I could build a string representation of the X'...' notation, then perhaps cast() would evaluate it. But as you can see, that just ends up spitting out X'3030303030303030313131343330423137454342' instead of the expected 00000000111430B17ECB. This means the concatenation is working correctly, but I can't find a way to evaluate it as hex the same was as in the manual test case.

I have been googling all morning to see if there is just some syntax I am missing, but the closest I have come is this concatenation using the || operator.

EDIT: Ultimately I just want to be able to have a simple case statement in my query like this:

SELECT  pd.model as HDModel,
  CASE
     WHEN pd.serial like "30303030%" THEN
         LTRIM(X'pd.serial')
     WHEN pd.serial like "202020%" THEN
         LTRIM(X'pd.serial')
  ELSE
     pd.serial
  END  as HDSerial

But because pd.serial gets wrapped in single quotes, it is taken as a literal string instead of taken as the data contained in that column. My hope was/is that there is just a character or operator I need to specify, like X'$pd.serial' or something.

END EDIT

If I can get past this first hurdle, my next task will be to try and remove the leading zeroes (the way LTRIM eats the leading spaces) and reverse the bytes, but to be honest, I would be content even if that part isn't possible because it wouldn't be hard to post-process this report in Excel to do that.

If anyone can point me in the right direction I would greatly appreciate it! It would obviously be much easier if I was using PHP or something else to do this processing, but because I am trying to have it be an embedded report in Spiceworks, I have to do this all in a single SQLite query.

解决方案

X'...' is the binary representation in sqlite. If the values are string, you can just use them as such.

This should be a start:

sqlite> select X'3030303030303030313131343330423137454342';
00000000111430B17ECB
sqlite> select ltrim(X'3030303030303030313131343330423137454342','0');
111430B17ECB

I hope this puts you on the right path.

这篇关于SQLite X'...'表示列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！