如何把一个字段,分成几个字段

Question

http://biancheng.dnbcw.info/mssql/164383.html  希望能帮到你

Answer 1

http://www.cnblogs.com/zfanlong1314/archive/2013/01/14/2859848.html

Answer 2

字符 串拆分。。substring .charindex stuff 各种函数组合

Answer 3

用PARSENAME函数拆分字符串
供参考！

Answer 4

试了一下邹老大的的确方便，另外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
GOSELECT 
    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
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
testa                                                                                                                                                                                                                                                            test1                                                                                                                                                                                                                                                            劳而无                                                                                                                                                                                                                                                              fadfad                                                                                                                                                                                                                                                           dfad
testb                                                                                                                                                                                                                                                            test2                                                                                                                                                                                                                                                            fadfa                                                                                                                                                                                                                                                            dfasdfa                                                                                                                                                                                                                                                          dfad
testc                                                                                                                                                                                                                                                            test3                                                                                                                                                                                                                                                            dfadfa                                                                                                                                                                                                                                                           fasdfasd                                                                                                                                                                                                                                                         dfadf*/

Answer 5

试了下，确实Parsename函数上限是处理4列！那只能用函数处理了！