在 T-SQL 中将单个字段值拆分为多个固定长度的列

问题描述

我在 SO 上查看了大约 15 个不同的答案，但还没有找到确切的情况.我正在做一个自定义数据导出，需要导出到一个数据文件，该文件将被导入到需要特定长度/格式的数据的旧系统中.我有一个备忘录"列，其中可以包含大量文本.我需要选择该值并将其拆分为多列，固定长度为 75 个字符.例如，如果我有一行包含 185 个字符的消息，我需要将其拆分为 3 个新的 75 个字符列，MEMO1、MEMO2、MEMO3，MEMO3 中的剩余空间用空格填充以等于 75 个字符.另一个问题是，我最多只能使用 18 个 75 字符的列来将数据转储到其中.如果长度超过 1350 (18x75) 个字符，其余的将被截断.

I've looked at about 15 different answers on SO but haven't found this exact situation yet. I'm doing a custom data export and need to export to a data file that will be imported into an older system that needs the data in a specific length/format. I have a "MEMO" column that can have a large amount of text in it. I need to select that value and split it into multiple columns with a FIXED length of 75 chars. For instance, if I have a row with a message that is 185 chars, I need to split that into 3 new columns of 75 chars, MEMO1, MEMO2, MEMO3, with the remaining space in MEMO3 being filled with spaces to equal the 75 chars. The other catch, I can only use up to 18 75-char columns to dump the data into. If it's longer than 1350 (18x75) chars, the rest gets truncated.

我尝试过这种方法，但它没有考虑新备忘录列的总数.我需要某种方法来迭代 NUMBEROFMEMOS 并只选择必要数量的新 MEMO 列，但显然您不能在选择中执行 WHILE.

I tried this approach, but it doesn't take the total number of new memo columns into consideration. I need some way to iterate over NUMBEROFMEMOS and only select the necessary amount of new MEMO columns, but apparently you can't do a WHILE in a select.

SELECT FIRSTNAME, 
       LASTNAME, 
       DOB, 
       CEILING(LEN(NOTETEXT) / 75.0) as NUMBEROFMEMOS,
       SUBSTRING(NOTETEXT, 1, 75) as MEMOLINE1,
       SUBSTRING(NOTETEXT, 76, 149) as MEMOLINE2,
       SUBSTRING(NOTETEXT, 150, 224) as MEMOLINE3,
       etc. etc. etc
FROM CUSTOMER

我是一名长期的应用程序开发人员，正在尝试更多地参与数据库方面的工作.如果我在 C# 世界中，我只会创建一个方法来执行直到 NUMBEROFMEMOS 的 for 循环并以这种方式输出数据.我认为这在这里行不通.提前致谢！

I'm a long-time application dev who is trying to get more involved in the DB side of things. If I were in the C# world, I would just create a method to do a for loop up to NUMBEROFMEMOS and output the data that way. I don't think that works here though. Thanks in advance!

推荐答案

您可以使用动态 SQL.下面是一个可以用来解决问题的示例:

You can use a dynamic SQL. Here you are an example you can use to solve your problem:

declare @text nvarchar(max) = N'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.';
declare @len int = 75;

declare @NUMBEROFMEMOS int = CEILING(LEN(@text) / @len);

declare @query nvarchar(max) = N'select ';

declare @loop int = 0;
declare @start int = 1;
declare @memoline int = 1;

while @loop <= @NUMBEROFMEMOS begin
    if @loop > 0 begin
        set @query += N', ';
    end

   set @query += N'substring(''' + @text + N''', ' + cast(@start as nvarchar(max)) + N', ' + cast(@len as nvarchar(max)) + N') as MEMOLINE' + cast(@memoline as  nvarchar(max));

   set @start += @len
   set @loop += 1;
   set @memoline += 1;
end

execute sp_sqlexec @query;

这篇关于在 T-SQL 中将单个字段值拆分为多个固定长度的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！