[转载]SQL 将一个字段内用逗号分隔的内容分成多条记录

[转载]SQL 将一个字段内用逗号分隔的内容分成多条记录 – 岩子 – 博客园.

由于业务需求,我们可能会把一串以分割符字符串数据放到一个字段,如我们在客户端处理拆分是很简单的,不过这样做效果不太好,怎么用SQL SERVER 2008 来解决这件事件哪?

方案1

SQL SERVER XML 功能来解决

<span class="pun">----&</span><span class="pln">gt</span><span class="pun">;</span><span class="pln"> </span><span class="pun">生成测试数据:</span><span class="pln"> </span><span class="lit">@T</span><span class="pln">
DECLARE </span><span class="lit">@T</span><span class="pln"> TABLE </span><span class="pun">(</span><span class="pln">id INT</span><span class="pun">,</span><span class="typ">Col1</span><span class="pln"VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="typ">Col2</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">))</span><span class="pln">
INSERT INTO </span><span class="lit">@T</span><span class="pln">
SELECT </span><span class="lit">1</span><span class="pun">,</span><span class="str">'卸货费'</span><span class="pun">,</span><span class="str">'运输车队业务, 客服接单业务'</span><span class="pln">
UNION ALL
SELECT </span><span class="lit">2</span><span class="pun">,</span><span class="str">'报关费'</span><span class="pun">,</span><span class="str">'报关业务、客服接单业务'</span><span class="pln">
 
</span><span class="pun">----</span><span class="pln">SQL</span><span class="pun">查询如下:</span><span class="pln">
 
SELECT
T</span><span class="pun">.</span><span class="pln">id</span><span class="pun">,</span><span class="pln">
A</span><span class="pun">.</span><span class="pln">x</span><span class="pun">.</span><span class="pln">value</span><span class="pun">(</span><span class="str">'.'</span><span class="pun">,</span><span class="str">'varchar(10)'</span><span class="pun">)</span><span class="pln"> AS code</span><span class="pun">,</span><span class="pln">
B</span><span class="pun">.</span><span class="pln">x</span><span class="pun">.</span><span class="pln">value</span><span class="pun">(</span><span class="str">'.'</span><span class="pun">,</span><span class="str">'varchar(10)'</span><span class="pun">)</span><span class="pln"> AS code2
FROM </span><span class="pun">(</span><span class="pln">
SELECT </span><span class="pun">*,</span><span class="pln">doc </span><span class="pun">=</span><span class="pln"> CONVERT</span><span class="pun">(</span><span class="pln">xml</span><span class="pun">,</span><span class="str">''</span><span class="pun">+</span><span class="pln">REPLACE</span><span class="pun">(</span><span class="pln">code</span><span class="pun">,</span><span class="str">','</span><span class="pun">,</span><span class="str">''</span><span class="pun">)+</span><span class="str">''</span><span class="pun">),</span><span class="pln">
doc2</span><span class="pun">=</span><span class="pln">CONVERT</span><span class="pun">(</span><span class="pln">xml</span><span class="pun">,</span><span class="str">''</span><span class="pun">+</span><span class="pln">REPLACE</span><span class="pun">(</span><span class="pln">code2</span><span class="pun">,</span><span class="str">','</span><span class="pun">,</span><span class="str">''</span><span class="pun">)+</span><span class="str">''</span><span class="pun">)</span><span class="pln">
FROM </span><span class="lit">@T</span><span class="pln">
</span><span class="pun">)</span><span class="pln"> AS T
CROSS APPLY doc</span><span class="pun">.</span><span class="pln">nodes</span><span class="pun">(</span><span class="str">'//v'</span><span class="pun">)</span><span class="pln"> AS A</span><span class="pun">(</span><span class="pln">x</span><span class="pun">)</span><span class="pln">
CROSS APPLY doc2</span><span class="pun">.</span><span class="pln">nodes</span><span class="pun">(</span><span class="str">'//v'</span><span class="pun">)</span><span class="pln"> AS B</span><span class="pun">(</span><span class="pln">x</span><span class="pun">)</span><span class="pln">
</span>

方案2

SQL SERVER WITH 递归查询

<span class="pln">WITH roy
AS </span><span class="pun">(</span><span class="pln"> SELECT </span><span class="typ">Col1</span><span class="pln"> </span><span class="pun">,</span><span class="pln">
</span><span class="typ">COl2</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> CAST</span><span class="pun">(</span><span class="pln">LEFT</span><span class="pun">(</span><span class="typ">Col2</span><span class="pun">,</span><span class="pln"> CHARINDEX</span><span class="pun">(</span><span class="str">','</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Col2</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">','</span><span class="pun">)</span><span class="pln"> </span><span class="pun">-</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln"> AS NVARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">))</span><span class="pln"> </span><span class="pun">,</span><span class="pln">
</span><span class="typ">Split</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> CAST</span><span class="pun">(</span><span class="pln">STUFF</span><span class="pun">(</span><span class="typ">COl2</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">','</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">,</span><span class="pln">
CHARINDEX</span><span class="pun">(</span><span class="str">','</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Col2</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">','</span><span class="pun">),</span><span class="pln"> </span><span class="str">''</span><span class="pun">)</span><span class="pln"> AS NVARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">))</span><span class="pln">
FROM </span><span class="typ">Tab</span><span class="pln">
UNION ALL
SELECT </span><span class="typ">Col1</span><span class="pln"> </span><span class="pun">,</span><span class="pln">
</span><span class="typ">COl2</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> CAST</span><span class="pun">(</span><span class="pln">LEFT</span><span class="pun">(</span><span class="typ">Split</span><span class="pun">,</span><span class="pln"> CHARINDEX</span><span class="pun">(</span><span class="str">','</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Split</span><span class="pun">)</span><span class="pln"> </span><span class="pun">-</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln"> AS NVARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">))</span><span class="pln"> </span><span class="pun">,</span><span class="pln">
</span><span class="typ">Split</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> CAST</span><span class="pun">(</span><span class="pln">STUFF</span><span class="pun">(</span><span class="typ">Split</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">,</span><span class="pln"> CHARINDEX</span><span class="pun">(</span><span class="str">','</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Split</span><span class="pun">),</span><span class="pln"> </span><span class="str">''</span><span class="pun">)</span><span class="pln"> AS NVARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">))</span><span class="pln">
FROM </span><span class="typ">Roy</span><span class="pln">
WHERE split </span><span class="pun">&</span><span class="pln">gt</span><span class="pun">;</span><span class="pln"> </span><span class="str">''</span><span class="pln">
</span><span class="pun">)</span><span class="pln">
SELECT </span><span class="typ">COl1</span><span class="pln"> </span><span class="pun">,</span><span class="pln">
</span><span class="typ">COl2</span><span class="pln">
FROM roy
ORDER BY </span><span class="typ">COl1</span><span class="pln">
OPTION </span><span class="pun">(</span><span class="pln"> MAXRECURSION </span><span class="lit">0</span><span class="pln"> </span><span class="pun">)</span><span class="pln">
</span>

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

登录

注册