SQL Server 2005 - 如何将图像数据类型转换为字符格式 [英] SQL Server 2005 - How do I convert image data type to character format

问题描述

后台：我是一名软件测试人员，使用测试用例管理数据库，该数据库使用已弃用的图像数据类型存储数据。我对SQL Server相对缺乏经验。

Background: I am a software tester working with a test case management database that stores data using the deprecated image data type. I am relatively inexperienced with SQL Server.

问题：富文本格式的字符数据存储为图像数据类型。目前，以人类可读格式查看此数据的唯一方法是通过我正在替换的测试用例管理工具本身。我知道没有直接的方法将图像数据类型转换为字符，但显然有一些方法可以实现，因为测试用例管理软件正在执行该任务。我搜索了这个网站，但没有发现任何点击。我还没有通过搜索网找到任何解决方案。

The problem: Character data with rich text formatting is stored as an image data type. Currently the only way to see this data in a human readable format is through the test case management tool itself which I am in the process of replacing. I know that there is no direct way to convert an image data type to character, but clearly there is some way this can be accomplished, given that the test case management software is performing that task. I have searched this site and have not found any hits. I have also not yet found any solutions by searching the net.

目标：我的目标是将数据导出SQL Server数据库到Access数据库中数据库中的行数少于10,000。在项目的后期阶段，Access数据库将升迁到SQL Server。

Objective: My goal is to export the data out of the SQL Server database into an Access database There are fewer than 10,000 rows in the database. At a later stage in the project, the Access database will be upsized to SQL Server.

请求：有人可以给我一个方法将图像数据类型转换为字符格式。

Request: Can someone please give me a method for converting the image data type to a character format.

。

推荐答案

一个解决方案（人类可读性）是将它从以二进制转换为字符数据的块中拉出来。如果每个字节都是有效的ASCII，那么应该没有问题（尽管遗留数据通常不是您所期望的）。

One solution (for human readability) is to pull it out in chunks that you convert from binary to character data. If every byte is valid ASCII, there shouldn't be a problem (although legacy data is often not what you expect).

首先，创建一个这样的表： / p>

First, create a table like this:

create table Nums(
  n int primary key
);

并插入从0到最小的整数（最大图像列长度，以字节为单位）/ 8000。然后，以下查询（未经测试，请仔细考虑）应以相对有用的形式获取数据。确保你将它拉到的客户端不会截断小于8000字节的字符串。 （如果你想在记事本中打开结果，可以做较小的块。）

and insert the integers from 0 up to at least (maximum image column length in bytes)/8000. Then the following query (untested, so think it through) should get your data out in a relatively useful form. Be sure whatever client you're pulling it to won't truncate strings at smaller than 8000 bytes. (You can do smaller chunks if you want to be opening the result in Notepad or something.)

SELECT
  yourTable.keycolumn,
  Nums.n as chunkPosition,
  CAST(SUBSTRING(imageCol,n*8000+1,8000) AS VARCHAR(8000)) as chunk
FROM yourTable
JOIN Nums
ON Nums.n <= (DATALENGTH(yourTable.imageCol)-1)/8000
ORDER BY yourTable.keycolumn, Nums.n

这篇关于SQL Server 2005 - 如何将图像数据类型转换为字符格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！