比如表字段A: a1,b2,c3,d4,e5,转换成多列呢!
--1. 测试数据:[Feng]
if object_id('[Feng]') is not null
drop table [Feng]
go
create table [Feng]
(
[TestName] nvarchar(30)
)
insert [Feng]
select 'a1,b2,c3,d4,e5' union all
select '1a,2b,3c,4d,5e' union all
select '2a,3b,4c,5d,6e'
--------------开始查询--------------------------
IF NOT OBJECT_ID('f_GetStr') IS NULL
DROP FUNCTION [f_GetStr]
GO
--分段截取函数
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000), --包含多个数据项的字符串
@pos int, --要获取的数据项的位置
@split varchar(10) --数据分隔符
)RETURNS varchar(1000)
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
GO
--执行结果
SELECT
dbo.f_GetStr([TestName],1,',') A,
dbo.f_GetStr([TestName],2,',') B,
dbo.f_GetStr([TestName],3,',') C,
dbo.f_GetStr([TestName],4,',') D,
dbo.f_GetStr([TestName],5,',') E
FROM [Feng]
----------------结果------------------
/*
A B C D E
a1 b2 c3 d4 e5
1a 2b 3c 4d 5e
2a 2b 3c 4d 5e B C D E
---------------------------------------
*/