表每个字段的内容如下a, b, c, d, 1|2|3|4, e
我希望查询出下面的形式, 就是有一个字段的内容里固定会出现3个|符号，能不能在SQL里按|截取出来分别显示在不同的字段里，|分割的内容里长度是不固定的所以不能按字符截取查询出的效果： a,b,c,d,1,2,3,4,e
我希望查询出下面的形式, 就是有一个字段的内容里固定会出现3个|符号，能不能在SQL里按|截取出来分别显示在不同的字段里，|分割的内容里长度是不固定的所以不能按字符截取查询出的效果： a,b,c,d,1,2,3,4,e
set @var='a, b, c, d, 1|2|3|4, e'
select replace(@var,'|',',')
--------------------------
a, b, c, d, 1,2,3,4, e(1 行受影响)
set @var='a, b, c, d, 1|2|3|4, e'
select replace(replace(@var,'|',','),' ','')
-------------------
a,b,c,d,1,2,3,4,e(1 行受影响)
/*****拆分一个字符串*********问题描述：@str='fds,bbbf,eee,ddd,fff,hhhfg,dddde' 拆分成col------------------------fdsbbbfeeedddfffhhhfgdddde*/---方法：动态declare @str varchar(500),@aaa varchar(8000)set @str='fds,bbbf,eee,ddd,fff,hhhfg,dddde' set @aaa='select * from (select '''+REPLACE(@str,',',''' as str union all select ''')+''') a 'exec(@aaa) go--方法：循环create table #t( id varchar(10)) declare @str varchar(300) set @str='fds,bbbf,eee,ddd,fff,hhhfg,dddde' declare @i int declare @len int set @i = 1 while @i < len(@str+',') begin insert #t select substring(@str+',',@i,charindex(',',@str+',',@i)-@i) set @i = charindex(',',@str+',',@i)+1 end select * from #tdrop table #tgo--orCREATE FUNCTION f_splitSTR(@s varchar(8000), --待分拆的字符串@split varchar(10) --数据分隔符)RETURNS @re TABLE(col varchar(100))ASBEGINDECLARE @splitlen intSET @splitlen=LEN(@split+'a')-2WHILE CHARINDEX(@split,@s)>0BEGININSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')ENDINSERT @re VALUES(@s)RETURNENDGO--方法：使用临时性分拆辅助表法CREATE FUNCTION f_splitSTR(@s varchar(8000), --待分拆的字符串@split varchar(10) --数据分隔符)RETURNS @re TABLE(col varchar(100))ASBEGIN--创建分拆处理的辅助表(用户定义函数中只能操作表变量)DECLARE @t TABLE(ID int IDENTITY,b bit)INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b
INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)FROM @tWHERE ID<=LEN(@s+'a') AND CHARINDEX(@split,@split+@s,ID)=IDRETURNENDGOdeclare @str varchar(500),@aaa varchar(8000)set @str='fds,bbbf,eee,ddd,fff,hhhfg,dddde' select * from dbo.f_splitSTR(@str,',')本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/feixianxxx/archive/2010/01/27/5263341.aspx
DROP FUNCTION [dbo].[f_GetStr]
GO
--分段截取函数(邹建)
CREATE FUNCTION dbo.f_GetStr
(
@s VARCHAR(8000), --包含多个数据项的字符串
@pos INT, --要获取的数据项的位置
@split VARCHAR(10) --数据分隔符
)
RETURNS VARCHAR(100)
AS
BEGIN
IF @s IS NULL
RETURN(NULL)
DECLARE @splitlen INT
SELECT @splitlen = LEN(@split+'a')-2
WHILE @pos>1 AND CHARINDEX(@split, @s+@split)>0
SELECT @pos = @pos-1,
@s = STUFF(@s, 1, CHARINDEX(@split, @s+@split)+@splitlen, '')
RETURN(ISNULL(LEFT(@s, CHARINDEX(@split, @s+@split)-1), ''))
END
GOdeclare @Type nvarchar(30)
set @Type = 'a, b, c, d, 1|2|3|4, e'SELECT [dbo].[f_GetStr](@Type,1,'|')a,[dbo].[f_GetStr](@Type,2,'|')b,
[dbo].[f_GetStr](@Type,3,'|')c,[dbo].[f_GetStr](@Type,4,'|')d
/*
a b c d
a, b, c, d, 1 2 3 4, e
*/
,x1=convert(xml,'<root><row>'+replace(x,'|','</row><row>')+'</row></root>').value('(root/row)[1]','nvarchar(10)')
,x2=convert(xml,'<root><row>'+replace(x,'|','</row><row>')+'</row></root>').value('(root/row)[2]','nvarchar(10)')
,x3=convert(xml,'<root><row>'+replace(x,'|','</row><row>')+'</row></root>').value('(root/row)[3]','nvarchar(10)')
,x4=convert(xml,'<root><row>'+replace(x,'|','</row><row>')+'</row></root>').value('(root/row)[4]','nvarchar(10)')
,e
from #test
a b c d x1 x2 x3 x4 e
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
a b c d 1 2 3 4 e (1 行受影响)这样。。
CREATE TABLE A
(
[STR] NVARCHAR(100)
)
INSERT INTO A ([STR])
SELECT 'A, B, C, D, 1|2|3|4, E' UNION ALL
SELECT 'AA,BB,CC,DD, 11|22|33|44, E'
--
SELECT REPLACE([STR],'|',', ') AS REPLACE_STR FROM A
表的各字段内容 ，有一个字段内容特许会有3个|符号分割
字段1 字段2 字段3 字段4 字段5 字段6
a b c d 1|2|3|4 e
查询后的效果
字段1 字段2 字段3 字段4 字段5 字段6 字段7 字段8 字段9
a b c d 1 2 3 4 e
字段1 字段2 字段3 字段4 字段5 字段6
a b c d 1|2|3|4 e
查询后的效果
字段1 字段2 字段3 字段4 字段5 字段6 字段7 字段8 字段9
a b c d 1 2 3 4 e
表的各字段内容 ，有一个字段内容特许会有3个|符号分割
字段1 字段2 字段3 字段4 字段5 字段6
a b c d 1|2|3|4 e
查询后的效果
字段1 字段2 字段3 字段4 字段5 字段6 字段7 字段8 字段9
a b c d 1 2 3 4 e
insert #test
select 'a','b','c','d','1|2|3|4','e'
select
a, b, c, d,
x1 = PARSENAME(REPLACE(x,'|','.'),4),
x2 = PARSENAME(REPLACE(x,'|','.'),3),
x3 = PARSENAME(REPLACE(x,'|','.'),2),
x4 = PARSENAME(REPLACE(x,'|','.'),1),
e
from
#test
a b c d x1 x2 x3 x4 e
---------- ---------- ---------- ---------- --- --- -- --- ---
a b c d 1 2 3 4 e(1 行受影响)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([字段1] varchar(1),[字段2] varchar(1),[字段3] varchar(1),[字段4] varchar(1),[字段5] varchar(11),[字段6] varchar(1))
insert [tb]
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e'
--1 加个序号，避免重复
select id=identity(int,1,1),* into #t from tb
--2 分解字段5
Select
a.id,a.字段1,a.字段2,a.字段3,a.字段4,
字段5=substring(a.字段5,b.number,charindex('|',a.字段5+'|',b.number)-b.number) ,a.字段6,
b.number
into #t1
from
#t a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.字段5)
where
substring('|'+a.字段5,b.number,1)='|'--3 加小组内的序号，避免重复
select *,id1=(select count(1) from #t1 where id=t.id and number<=t.number)
into #t2
from #t1 t--4 合并显示
declare @sql varchar(8000)
set @sql = 'select 字段1,字段2,字段3,字段4 '
select @sql = @sql + ' , max(case id1 when ' + ltrim(id1) + ' then 字段5 else null end) [字段' +ltrim(id1+4) + ']'
from (select distinct id1 from #t2) as a
set @sql = @sql + ',字段6 as 字段'+(select ltrim(count(distinct id1)+5) from #t2)+' from #t2 group by id,字段1,字段2,字段3,字段4,字段6'
exec(@sql)--结果
/*
字段1 字段2 字段3 字段4 字段5 字段6 字段7 字段8 字段9
---- ---- ---- ---- ----------- ----------- ----------- ----------- ----
a b c d aaa bb cc d e
a b c d aaa bb cc d e
a b c d aaa bb cc d e
a b c d aaa bb cc d e
a b c d aaa bb cc d e（所影响的行数为 5 行）警告: 聚合或其他 SET 操作消除了空值。
*/