27,579
社区成员
发帖
与我相关
我的任务
分享
ID Content
1 我是一只小小小小鸟|花太香|一生有你|草原香
2 我是一只小小小小鸟|花太香|一生有你|草原香
3 我是一只小小小小鸟|花太香|一生有你|草原香
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-07 11:16:19
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[Content] varchar(41))
insert [huang]
select 1,'我是一只小小小小鸟|花太香|一生有你|草原香' union all
select 2,'我是一只小小小小鸟|花太香|一生有你|草原香' union ALL
select 3,'我是一只小小小小鸟|花太香|一生有你|草原香'
--------------开始查询--------------------------
;WITH cte AS (
SELECT id,CONTENT
FROM (
select
id,
SUBSTRING([Content],number,CHARINDEX('|',[Content]+'|',number)-number) as [Content]
from
[huang] a,master..spt_values
where
number >=1 and number<=len([Content])
and type='p'
and substring('|'+[Content],number,1)='|')a
WHERE LEN(CONTENT)<=4)
select a.id,
stuff((select '|'+CONTENT from cte b
where b.id=a.id
for xml path('')),1,1,'') 'CONTENT'
from cte a
group by a.id
----------------结果----------------------------
/*
id CONTENT
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 花太香|一生有你|草原香
2 花太香|一生有你|草原香
3 花太香|一生有你|草原香
*/