羽毛球

生活在别处

导航

<2010年3月>
日一二三四五六
28123456
78910111213
14151617181920
21222324252627
28293031123
45678910

统计

留言簿(29)

随笔分类

随笔档案

文章分类

文章档案

搜索

最新评论

阅读排行榜

评论排行榜

SQL Server中字符串类型(varchar)和binary的转换

一个数据库表A中有一列: guid_text char(36)，保存着类似'00112FEA1624-44162203-02E3-1CC5-CE02'的guid（自定义的算法）。
表中另外一列用的是binary(16)类型。
希望实现的功能是在SQL脚本中实现char(36)到binary(16)的转换， 例如转换'00112FEA1624-44162203-02E3-1CC5-CE02' 为 0x00112FEA16244416220302E31CC5CE02。

cast或convert函数不能完成这样的转换。
c++程序中实现这样的转换很容易，但不能直接将这样的函数置于sql语句中执行批量update。
似乎没有什么好办法，最后只能用一个笨办法，直接把字符串中每两个字节转为一个binary字节。这可以通过sql server自定义函数实现（no error handle)。
create function fnGetBinary
    (@strGUID varchar(40))
returns varbinary(40)
as
begin    
    declare @varBinGuid varbinary(40)
    declare @iTextPos tinyint
    set @iTextPos = 0

--    -     45
--    0~9    48
--    A~F    65
    declare @iNum tinyint
    set @iNum = 0
    while (@iNum < 16)
    begin
        declare @asciiVal tinyint
        declare @oneByte tinyint
        set @asciiVal = ascii(right(@strGUID, 36-@iTextPos))
        if @asciiVal != 45
        begin
            if @asciiVal < 65
            set @oneByte = @asciiVal - 48
            else
            set @oneByte = @asciiVal - 65 + 10
           
                   set @iTextPos = @iTextPos + 1

            set @asciiVal = ascii(right(@strGUID, 36-@iTextPos))
            if @asciiVal < 65
            set @oneByte = @oneByte*16 + @asciiVal - 48
            else
            set @oneByte = @oneByte*16 + @asciiVal - 65 + 10

                   set @iTextPos = @iTextPos + 1
            if @iNum = 0
            set @varBinGuid = cast(@oneByte as binary(1))
            else
                set @varBinGuid = @varBinGuid + cast(@oneByte as binary(1))
            set @iNum = @iNum + 1
        end
        else
            set @iTextPos = @iTextPos + 1
    end

    return @varBinGuid
end
然后我们就可以直接在sql语句中使用这个函数。
update xxxA set guid = dbo.fnGetBinary(guid_t)
由于不用一条一条取出记录，再更新，速度会有数量级的提高；表越大，对比越明显。


后记：如果用SQL Server专门处理guid的数据类型uniqueidentifier，会方便一些。
           通过uniqueidentifier作为桥梁，可以方便实现guid从binary类型转为char类型。
declare @tmpText varchar(36)
set @tmpText = '4B097C43-66A8-49c0-A6D1-3194457C5B22'
--right, can convert char to binary by uniqueidentidier
select cast(@tmpText as uniqueidentifier), cast(cast(@tmpText as uniqueidentifier) as binary(16))

--wrong, can't convert char to binary directly
select cast(@tmpText as binary(16))

posted on 2006-03-29 11:57 Michael 阅读(3860) 评论(2)  编辑 收藏

评论

# re: SQL Server中字符串类型(varchar)和binary的转换 2008-05-27 11:54 健康管理

好东西，收藏了

# re: SQL Server中字符串类型(varchar)和binary的转换 2009-05-06 12:02 虚拟主机

一家提供域名注册的公司，提供虚拟主机，为企业上网提供最佳虚拟主机解决方案，另外还有服务器租用，服务器托管等相关业务。

标题  
姓名  
主页
验证码 *
内容   
  登录  使用高级评论  Top
[使用Ctrl+Enter键可以直接提交]