在tsql中将基本16个字符串转换为基本2个字符串 [英] Convert base 16 strings to base 2 strings in tsql

问题描述

我不是特别擅长处理数据库，但是有没有办法将二进制数据表示为以2为基数的字符串呢?我使用TSQL.无论如何，我最多只能将二进制值表示为以16为基数的字符串.例如，我在一个单元格中有 500 ，我希望将其显示/处理为"0b111110100"而不是"0x01F4".这个

I am not particularly good at dealing with databases, but is there a way to represent binary data as a sort of base 2 strings? I use TSQL. Whatever I do I get binary values represented as base 16 strings at best. For example, I have, lets say, 500 in a cell and I want it to be displayed/treated as '0b111110100' rather than '0x01F4'. This

 CONVERT(VARCHAR, cast(500 as binary(2)), 1)

给出了 0x01F4 ，而我想获得 111110100 之类的东西.

gives 0x01F4 whereas I would like to get 111110100 or the like.

我没有找到任何合适的内置函数.但是，我遇到了一个可怕的过程，将基数为16的字符串转换为基数为2的字符串非常复杂.我也可以考虑对值使用嵌套的 replace()调用，以将 F 替换为 1111 等，但是感觉不对..

I didn't find any suitable built-in functions. However, I came across one dreadful and quite complicated procedure to convert base 16 strings into base 2 strings. Also I could think about using nested replace() calls on the values to replace F with 1111 etc., but it doesn't feel right.

整个目的是查找以二进制表示形式连续出现至少4个 1 的值.我的方法:如果我可以使它们看起来像"0b111110100"，则就像 LIKE％1111％这样简单.还是我做错了?

The whole purpose is to find values with at least 4 1s in a row in their binary representation. My approach: if I could make them look like '0b111110100', it would be as simple as LIKE %1111%. Or am I doing it wrong?

推荐答案

牢记我的最初目标:

整个目的是查找以二进制表示的行中至少包含4个1的值.

The whole purpose is to find values with at least 4 1s in a row in their binary representation.

我采用了另一条路线(没有建议的功能)，并且可以提出这个解决方案-首先，我通过

I took another route (without suggested functions) and could come up with this solution - first, I used recursion via Common Table Expressions to get the binary representation of needed values like this:

with cte as(
    select 1 code, 29/2 n, 29 - 2*(29/2) bit_val
    union all
    select code, n/2, n - 2*(n/2) from cte
    where n > 0
)
select  code, row_number() over(partition by code order by n desc) 
bit_no, bit_val from cte

这给了我29的二进制表示形式(例如):

which gave me the binary representation of 29 (for example):

-- code is an id of entity where the value I am trying to convert into binary belongs to
---------------------------
| code | bit_no | bit_val |
---------------------------
|  12  |   1    |    1    |
|  12  |   2    |    0    | 
|  12  |   3    |    1    |
|  12  |   4    |    1    |
|  12  |   5    |    1    |
---------------------------

然后我只是在4行窗口中使用了聚合函数(SUM):

Then I just used an aggregate function (SUM) within a 4-rows window:

select *, sum(bit_val) over(partition by code order by bit_no 
rows between current row and 3 following) bits_in_a_row from cte1

-------------------------------------------
| code | bit_no | bit_val | bits_in_a_row |
-------------------------------------------
|  12  |   1    |    1    |       3       |
|  12  |   2    |    0    |       3       | 
|  12  |   3    |    1    |       3       |
|  12  |   4    |    1    |       2       |
|  12  |   5    |    1    |       1       |
-------------------------------------------

如果连续4行中的单元格总数为4-我得到了我想要的内容，尽管它并不是将最初的16位字符串转换为2位字符串的方法，就像我最初在标题中输入的那样.仅当 bits_in_a_row 等于4(或其他任何值)的身份时，才能获得最终答案.

If the sum of cells in 4 consecutive rows is 4 - I got what I was looking for although it is not quite a conversion of base 16 strings to base 2 strings as I initially put it in the caption. The final answer can be achieved taking only identities where bits_in_a_row is equal to 4 (or whatever else).

这篇关于在tsql中将基本16个字符串转换为基本2个字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！