Netezza SQL将VARCHAR转换为二进制字符串 [英] Netezza SQL convert VARCHAR to binary string
问题描述
我有一个位图存储为Netteza中的VARCHAR.需要在Netezza中将VARCHAR转换为二进制字符串.
I have a bitmap stored as a VARCHAR in Netteza. Need to convert that VARCHAR to a binary string in Netezza.
输入(Netezza col值-VARCHAR)= '0xFFFFFFFFFFFFFFFF'
Input (Netezza col value - VARCHAR ) = '0xFFFFFFFFFFFFFFFF'
所需的输出(VARCHAR)->
'1111111111111111111111111111111111111111111111111111111111111111'
是否可以使用Netezza查询来做到这一点?
Is there a way to do this using Netezza query ?
我尝试了
SELECT CAST('0xFFFFFFFFFFFFFFFF' AS VARBINARY(64) );
但这会引发错误
错误[HY000]错误:无法将类型'VARCHAR'强制转换为'VARBINARY'
ERROR [HY000]ERROR: Cannot cast type 'VARCHAR' to 'VARBINARY'
推荐答案
您可以将十六进制字符串转换为二进制数据，并将其存储在VARCHAR或VARBINARY列中.我倾向于VARCHAR，因为VARBINARY可用的CAST非常有限.
You can convert a hex string into binary data and store it in either a VARCHAR or VARBINARY column. I tend to prefer VARCHAR because of the rather limited CASTs that are available for VARBINARY.
要将十六进制字符串转换为二进制并将其存储在VARCHAR中，请使用SQL Extension Toolkit附带的hextoraw函数.这是Netezza附带的，但必须由管理员配置并使其可用.
To convert a hex string to binary and stored it in a VARCHAR, use the hextoraw function provided with the SQL Extension Toolkit. This is included with Netezza but must be configured and made available by your administrator.
要将十六进制字符串转换为二进制并将其存储在VARBINARY中，请使用Netezza随附的hex_to_binary函数(在7.2版中添加).
To convert a hex string to binary and store it in a VARBINARY, use the hex_to_binary function included with Netezza (added in v 7.2).
drop table test_table if exists;
DROP TABLE
create table test_table (col1 varchar(50), col2 varbinary(50));
CREATE TABLE
insert into test_table values (hextoraw('464F4F'), hex_to_binary('464F4F'));
INSERT 0 1
select * from test_table;
COL1 | COL2
------+-----------
FOO | X'464F4F'
(1 row)
从那里开始，您将需要一个UDF来处理您要进行的位计算.我整理了三个简单的UDF，我相信它们会适合您的目的.
From there you'll need a UDF to handle the bit calculations that you want to do. I've put together three simple UDFs that I believe will suit your purpose.
FirstBit返回第一个非零位的位置. BitCount返回非零位的总数. CharToBase2将VARCHAR中的二进制值转换为1和0.
FirstBit returns the position of the first non-zero bit. BitCount returns the total count of non-zero bits. CharToBase2 converts a binary values in a VARCHAR of 1s and 0s.
我认为前两个可以得到您需要的最终结果，而没有第三个，但是如果您仍然想要它，就在这里.
I think the first two get the end result that you need without the third, but in case you still wanted that, it's here.
select firstbit(hextoraw('0000')), bitcount(hextoraw('0000')), chartobase2(hextoraw('0000'));
FIRSTBIT | BITCOUNT | CHARTOBASE2
----------+----------+------------------
-1 | 0 | 0000000000000000
(1 row)
select firstbit(hextoraw('0001')), bitcount(hextoraw('0001')), chartobase2(hextoraw('0001'));
FIRSTBIT | BITCOUNT | CHARTOBASE2
----------+----------+------------------
15 | 1 | 0000000000000001
(1 row)
select firstbit(hextoraw('FFFF')), bitcount(hextoraw('FFFF')), chartobase2(hextoraw('FFFF'));
FIRSTBIT | BITCOUNT | CHARTOBASE2
----------+----------+------------------
0 | 16 | 1111111111111111
(1 row)
这里是每个的来源.请注意，我是一个糟糕的C ++编码器，如果那是我的工作，很可能会被解雇，所以请警告.
Here are the sources for each. Please note that I am a terrible C++ coder, and would likely be fired if that were my job, so caveat emptor.
BitCount.cpp
#include "udxinc.h"
#include <string.h>
using namespace nz::udx;
class BitCount : public Udf
{
public:
static Udf* instantiate();
ReturnValue evaluate()
{
StringArg* str = stringArg(0);
int32 retval = 0;
for(int i=0; i< str->length; i++)
{
for (int y=7; y>=0 ; y--)
{
if ((str->data[i] & (unsigned char)pow(2,y)) > 0)
{
retval++;
}
}
}
NZ_UDX_RETURN_INT32(retval);
}
};
Udf* BitCount::instantiate()
{
return new BitCount;
}
FirstBit.cpp
#include "udxinc.h"
#include <string.h>
using namespace nz::udx;
class FirstBit : public Udf
{
public:
static Udf* instantiate();
ReturnValue evaluate()
{
StringArg* str = stringArg(0);
int32 retval = -1;
for(int i=0; i< str->length; i++) {
for (int y=7; y>=0 ; y--) {
if ((str->data[i] & (unsigned char)pow(2,y)) > 0)
{
retval = i*8 + 7 - y;
}
if (retval > -1) break;
}
if (retval > -1) break;
}
NZ_UDX_RETURN_INT32(retval);
}
};
Udf* FirstBit::instantiate()
{
return new FirstBit;
}
CharToBase2.cpp
#include "udxinc.h"
#include <string.h>
using namespace nz::udx;
class CharToBase2 : public Udf
{
public:
static Udf* instantiate();
ReturnValue evaluate()
{
StringArg* str = stringArg(0);
StringReturn* result = stringReturnInfo();
result->size = str->length*8;
//unsigned char stringbyte = 0 ;
for(int i=0; i< str->length; i++)
{
for (int y=7; y>=0 ; y-- )
{
if ((str->data[i] & (unsigned char)pow(2,y)) == 0) {
result->data[i*8 + 7 - y] = '0'; }
else {
result->data[i*8 + 7 - y] = '1'; }
}
}
NZ_UDX_RETURN_STRING(result);
}
uint64 calculateSize() const
{
return sizerStringSizeValue(sizerStringArgSize(0)*8);
}
};
Udf* CharToBase2::instantiate()
{
return new CharToBase2;
}
最后，这是我用来编译和安装每个脚本的脚本.
Finally, here are the scripts I used to compile and install each.
install_firstbit.sh DBNAME
DB=$1
if [[ -z $DB ]]; then
DB=$NZ_DATABASE
fi
if [[ -z $DB ]]; then
print "Usage: install <database>"
return 1
fi
export NZ_DATABASE="${DB}"
nzudxcompile FirstBit.cpp \
--fenced \
--sig "FirstBit(varchar(any))" \
--return "integer" \
--class "FirstBit"
rm FirstBit.o_*
install_bitcount.sh DBNAME
DB=$1
if [[ -z $DB ]]; then
DB=$NZ_DATABASE
fi
if [[ -z $DB ]]; then
print "Usage: install <database>"
return 1
fi
export NZ_DATABASE="${DB}"
nzudxcompile BitCount.cpp \
--fenced \
--sig "BitCount(varchar(any))" \
--return "integer" \
--class "BitCount"
rm BitCount.o_*
install_chartobase2.sh DBNAME
DB=$1
if [[ -z $DB ]]; then
DB=$NZ_DATABASE
fi
if [[ -z $DB ]]; then
print "Usage: install <database>"
return 1
fi
export NZ_DATABASE="${DB}"
nzudxcompile CharToBase2.cpp \
--fenced \
--sig "CharToBase2(varchar(any))" \
--return "varchar(any)" \
--class "CharToBase2"
rm CharToBase2.o_*
这篇关于Netezza SQL将VARCHAR转换为二进制字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
