将逗号分隔的字符串转换为sql server中的多列 [英] Converting comma delimited string to multiple columns in sql server

问题描述

我想提取以逗号分隔的特定字符串，并跨SQL Server 2008中的特定列进行解析.SQL Server中的表结构如下:

I want to extract specific strings separated by a comma and parse across the specific columns in SQL server 2008. The table structure in SQL server is as follows:

CREATE TABLE SAMP(COMMASEPA VARCHAR(255),X VARCHAR(10),Y VARCHAR(10),Z VARCHAR(10),A VARCHAR(10),B VARCHAR(10),C VARCHAR(10),D VARCHAR(10))
INSERT INTO SAMP VALUES('X=1,Y=2,Z=3',null,null,null,null,null,null,null),
('X=3,Y=4,Z=5,A=6',null,null,null,null,null,null,null),
('X=1,Y=2,Z=3,A=5,B=6,C=7,D=8',null,null,null,null,null,null,null)

我希望基于逗号和 [x/y/z/a/b/c/d] 中的字符串之一分隔字符串.例如，在第一行的结果表中，X=1 应该在 X 列中，Y=2 应该在 Y 列中，Z=3 应该在 Z 列中.请输入任何想法.谢谢……

I want the string to be separated based on comma and ONE of the strings in [x/y/z/a/b/c/d]. For example in the result table for first row X=1 should be in X col, Y=2 should be in Y col, Z=3 should be in Z col. Please input any ideas in doing this. Thank you…

推荐答案

你可以在 SQL Fiddle 上看到这个:http://sqlfiddle.com/#!3/8c3ee/32

You can see this working on SQL Fiddle: http://sqlfiddle.com/#!3/8c3ee/32

这是它的主要内容:

with parsed as (
  select
  commasepa,
  root.value('(/root/s/col[@name="X"])[1]', 'varchar(20)') as X,
  root.value('(/root/s/col[@name="Y"])[1]', 'varchar(20)') as Y,
  root.value('(/root/s/col[@name="Z"])[1]', 'varchar(20)') as Z,
  root.value('(/root/s/col[@name="A"])[1]', 'varchar(20)') as A,
  root.value('(/root/s/col[@name="B"])[1]', 'varchar(20)') as B,
  root.value('(/root/s/col[@name="C"])[1]', 'varchar(20)') as C,
  root.value('(/root/s/col[@name="D"])[1]', 'varchar(20)') as D
FROM
(
select
   commasepa,
   CONVERT(xml,'<root><s><col name="' + REPLACE(REPLACE(COMMASEPA, '=', '">'),',','</col></s><s><col name="') + '</col></s></root>') as root
FROM
  samp
) xml
)
update 
  samp
  set
  samp.x = parsed.x,
  samp.y = parsed.y,
  samp.z = parsed.z,
  samp.a = parsed.a,
  samp.b = parsed.b,
  samp.c = parsed.c,
  samp.d = parsed.d
from
  parsed
where
  parsed.commasepa = samp.commasepa;

完全披露 - 我是 sqlfiddle.com 的作者

Full disclosure - I'm the author of sqlfiddle.com

首先将每个commasepa字符串转换为如下所示的XML对象:

This works by first converting each commasepa string into an XML object that looks like this:

<root>
 <s>
  <col name="X">1</col>
 </s>
 <s>
  <col name="Y">2</col>
 </s>
  ....
</root>

一旦我有了那种格式的字符串，我就使用 SQL Server 2005(及更高版本)支持的 xquery 选项，即 .value('(/root/s/col[@name="X"])[1]', 'varchar(20)') 部分.我单独选择每个潜在列，以便在可用时对其进行标准化和填充.使用这种规范化格式，我使用称为解析"的通用表表达式 (CTE) 定义结果集.然后将此 CTE 连接回更新语句中，以便可以在原始表中填充值.

Once I have the string in that format, I then use the xquery options that SQL Server 2005 (and up) support, which is the .value('(/root/s/col[@name="X"])[1]', 'varchar(20)') part. I select each of the potential columns individually, so they are normalized and populated when available. With that normalized format, I define the result set with a Common Table Expression (CTE) that I called 'parsed'. This CTE is then joined back in the update statement, so that the values can be populated in the original table.

这篇关于将逗号分隔的字符串转换为sql server中的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！