列test中数据格式为
testa----test1----劳而无----fadfad----dfad
testb----test2----fadfa----dfasdfa----dfad
testc----test3----dfadfa----fasdfasd----dfadf
每行数据 都有分割符号---- 。
现在想将test里的数据分割到 a ,b,c,d,e 列中。
如下图所示:
a b c d e
testa test1 劳而无 fadfad dfad
testb test2 fadfa fadfa dfad
testc test3 dfadfa fasdfasd dfadf
------解决方案--------------------
http://biancheng.dnbcw.info/mssql/164383.html
希望能帮到你
------解决方案--------------------
http://www.cnblogs.com/zfanlong1314/archive/2013/01/14/2859848.html
------解决方案--------------------
字符 串拆分。。substring .charindex stuff 各种函数组合
------解决方案--------------------
用PARSENAME函数拆分字符串
供参考！
------解决方案--------------------
试了一下邹老大的的确方便，另外Parsename上限是处理4列
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-08 07:53:44
-- 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]([test] varchar(45))
insert [huang]
select 'testa----test1----劳而无----fadfad----dfad' union all
select 'testb----test2----fadfa----dfasdfa----dfad' union all
select 'testc----test3----dfadfa----fasdfasd----dfadf'
--------------开始查询--------------------------
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(test,1,'----') A,
dbo.f_GetStr(test,2,'----') B,
dbo.f_GetStr(test,3,'----') C,
dbo.f_GetStr(test,4,'----') D,
dbo.f_GetStr(test,5,'----') E
FROM huang
----------------结果----------------------------
/*
A B C D E