将单列拆分为多个并将其加载到表或视图中 [英] Split Single Column into multiple and Load it to a Table or a View

问题描述

我正在使用SQL Server2008.我有一个源表，其中有几列(A，B)，其中包含要拆分为多列的字符串数据.我确实具有执行已写入拆分的功能.

I'm using SQL Server 2008. I have a source table with a few columns (A, B) containing string data to split into a multiple columns. I do have function that does the split already written.

源表中的数据(无法修改源表格式)在正在创建的视图中使用.但是我需要让View已经从Source表中拆分A列和B列的数据.因此，我的视图将具有不在Source表中的额外列.

The data from the Source table (the source table format cannot be modified) is used in a View being created. But I need to have my View have already split data for Column A and B from the Source table. So, my view will have extra columns that are not in the Source table.

然后，将用源表填充的视图用于与其他表合并.

Then the View populated with the Source table is used to Merge with the Other Table.

此处有两个问题:

1. 创建视图时可以从源表中拆分列A和B，但不更改源表吗?

1. Can I split column A and B from the Source table when creating a View, but do not change the Source Table?

如何在选择"视图中使用现有的用户定义函数来完成此任务?

How to use my existing User Defined Function in the View "Select" statement to accomplish this task?

简而言之:

要拆分的字符串也显示在注释掉的部分的示例中.几乎有目标表，vStandardizedData View，SP，它使用视图"数据合并到tblStandardizedData表.因此，在源"列中，我有A列和B列，在加载到tblStandardizedData表之前需要对其进行拆分.

String to split is also shown in the example in the commented out section. Pretty much have Destination table, vStandardizedData View, SP that uses the View data to Merge to tblStandardizedData table. So, in my Source column I have column A and B that I need to split before loading to tblStandardizedData table.

我正在处理五个对象:

1. 源文件
2. 目的地表
3. vStandardizedData视图
4. tblStandardizedData表
5. 合并的存储过程 (更新和插入)形成vStandardizedData视图.

1. Source File
2. Destination Table
3. vStandardizedData View
4. tblStandardizedData table
5. Stored procedure that does merge (Update and Insert) form the vStandardizedData View.

注意:所有5个对象均按应被创建和加载的顺序列出.

Note: all the 5 objects a listed in the order they are supposed to be created and loaded.

与此分开的是，有一个现有的UDFunction可以拆分被告知要使用的字符串

Separately from this there is an existing UDFunction that can split the string which I was told to use

要拆分的A列中的字符串示例(B列具有相同的格式数据):

Example of the string in column A (column B has the same format data) to be split:

6667 Mission Street, 4567 7rd Street, 65 Sully Pond Park

所需结果:

用户定义的函数返回表变量:

User-defined function returns a table variable:

CREATE FUNCTION [Schema].[udfStringDelimeterfromTable]
(
    @sInputList VARCHAR(MAX) -- List of delimited items
  , @Delimiter CHAR(1) = ',' -- delimiter that separates items
)   
RETURNS @List TABLE (Item VARCHAR(MAX)) WITH SCHEMABINDING
/* 
* Returns a table of strings that have been split by a delimiter.
* Similar to the Visual Basic (or VBA) SPLIT function. The 
* strings are trimmed before being returned.  Null items are not
* returned so if there are multiple separators between items, 
* only the non-null items are returned.
* Space is not a valid delimiter.
*
* Example:
SELECT * FROM [Schema].[udfStringDelimeterfromTable]('abcd,123, 456, efh,,hi', ',')
*
* Test:
DECLARE @Count INT, @Delim CHAR(10), @Input VARCHAR(128)
SELECT @Count = Count(*) 
    FROM  [Schema].[udfStringDelimeterfromTable]('abcd,123, 456', ',')
PRINT 'TEST 1 3 lines:' + CASE WHEN @Count=3 
              THEN 'Worked' ELSE 'ERROR' END
SELECT @DELIM=CHAR(10)
     , @INPUT = 'Line 1' + @delim + 'line 2' + @Delim
SELECT @Count = Count(*) 
    FROM  [Schema].[udfStringDelimeterfromTable](@Input, @Delim)
PRINT 'TEST 2  LF    :' + CASE WHEN @Count=2 
              THEN 'Worked' ELSE 'ERROR' END

推荐答案

我要问你的是阅读此内容:如何创建一个最小，完整和可验证的示例.

通常:如果使用UDF，则会获取逐表数据的数据.最好是，如果您的UDF会返回该项目以及一个运行编号.否则，您首先需要使用ROW_NUMBER() OVER(...)创建部件号，以便通过字符串串联创建目标列名称.然后使用PIVOT并排获取列.

In general: If you use your UDF, you'll get table-wise data. It was best, if your UDF would return the item together with a running number. Otherwise you'll first need to use ROW_NUMBER() OVER(...) to create a part number in order to create your target column names via string concatenation. Then use PIVOT to get the columns side-by-side.

一种更简单的方法是通过XML进行字符串拆分，例如在此答案中

An easier approach could be a string split via XML like in this answer

概念快速展示原理:

DECLARE @tbl TABLE(ID INT,YourValues VARCHAR(100));
INSERT INTO @tbl VALUES
 (1,'6667 Mission Street, 4567 7rd Street, 65 Sully Pond Park')
,(2,'Other addr1, one more addr, and another one, and even one more');

WITH Casted AS
(
    SELECT *
          ,CAST('<x>' + REPLACE(YourValues,',','</x><x>') + '</x>' AS XML) AS AsXml
    FROM @tbl
)
SELECT *
      ,LTRIM(RTRIM(AsXml.value('/x[1]','nvarchar(max)'))) AS Address1
      ,LTRIM(RTRIM(AsXml.value('/x[2]','nvarchar(max)'))) AS Address2
      ,LTRIM(RTRIM(AsXml.value('/x[3]','nvarchar(max)'))) AS Address3
      ,LTRIM(RTRIM(AsXml.value('/x[4]','nvarchar(max)'))) AS Address4
      ,LTRIM(RTRIM(AsXml.value('/x[5]','nvarchar(max)'))) AS Address5
FROM Casted

如果您的值中可能包含禁止使用的字符(尤其是<,> and &)，则可以在链接的答案中找到解决此问题的方法.

If your values might include forbidden characters (especially <,> and &) you can find an approach to deal with this in the linked answer.

结果

+----+---------------------+-----------------+--------------------+-------------------+----------+
| ID | Address1            | Address2        | Address3           | Address4          | Address5 |
+----+---------------------+-----------------+--------------------+-------------------+----------+
| 1  | 6667 Mission Street | 4567 7rd Street | 65 Sully Pond Park | NULL              | NULL     |
+----+---------------------+-----------------+--------------------+-------------------+----------+
| 2  | Other addr1         | one more addr   | and another one    | and even one more | NULL     |
+----+---------------------+-----------------+--------------------+-------------------+----------+

这篇关于将单列拆分为多个并将其加载到表或视图中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！