将字段内容如何按固定字符拆分？

Question

将字段内容如何按固定字符拆分？
有一列的内容是这样的，
AAAA-bbbb-cccc-dddd
我想分别将用-区别的几项取出来 ，
比如想分别得到
AAAA
bbbb
cccc
dddd如何可以实现呢？

Answer 1

while charindex('-',字段+'-')>0
  begin
  end

Answer 2

如果只是这样,三个分割符,可用parsename函数.

Answer 3

declare @t nvarchar(1000)
set @t=N'AAAA-bbbb-cccc-dddd'
create table tbd(id int identity(1,1),name nvarchar(100))while CHARINDEX('-',@t+'-')>1
begin
  insert tbd(name) select LEFT(@t,charindex('-',@t+'-')-1)
  set @t=STUFF(@t,1,charindex('-',@t+'-'),N'')
end
select * from tbd/*
id name
1 AAAA
2 bbbb
3 cccc
4 dddd
*/最简单的一种，最好用临时表处理！

Answer 4

declare @s as varchar(50)
set @s = 'AAAA-bbbb-cccc-dddd'select parsename(replace(@s,'-','.'),4)
union all
select parsename(replace(@s,'-','.'),3)
union all
select parsename(replace(@s,'-','.'),2)
union all
select parsename(replace(@s,'-','.'),1)/*
                                                                                                                                 
-------------------------------------------------------------------------------------------------------------------------------- 
AAAA
bbbb
cccc
dddd（所影响的行数为 4 行）
*/

Answer 5

AAAA-bbbb-cccc-dddd 
其们-大家-wqadf9-我们
878-343-99134-你是谁
好-人-的-有
我想一次性得到
cccc
wqadf9
99134
的如何实现？

Answer 6

declare @str varchar(8000) set @str = 'AAAA-bbbb-cccc-dddd ' set @str =  'select  name='''+replace(@str,'-',''''+' union all select ''')+'''' exec(@str)
/*name
-----
AAAA
bbbb
cccc
dddd (4 行受影响)
*/

Answer 7

select parsename(replace(字段,'-','.'),2) from tb

Answer 8

create table tb(s varchar(50))
insert into tb values('AAAA-bbbb-cccc-dddd') 
insert into tb values('其们-大家-wqadf9-我们 ')
insert into tb values('878-343-99134-你是谁') 
insert into tb values('好-人-的-有') 
go
select parsename(replace(s,'-','.'),4),
       parsename(replace(s,'-','.'),3),
       parsename(replace(s,'-','.'),2),
       parsename(replace(s,'-','.'),1)
from tb
/*
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- 
AAAA                                                                                                                             bbbb                                                                                                                             cccc                                                                                                                             dddd
其们                                                                                                                               大家                                                                                                                               wqadf9                                                                                                                           我们 
878                                                                                                                              343                                                                                                                              99134                                                                                                                            你是谁
好                                                                                                                                人                                                                                                                                的                                                                                                                                有（所影响的行数为 4 行）
*/drop table tb

Answer 9

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-09-21 11:22:43
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col] varchar(21))
insert [tb]
select 'AAAA-bbbb-cccc-dddd' union all
select '其们-大家-wqadf9-我们' union all
select '878-343-99134-你是谁' union all
select '好-人-的-有'
--------------开始查询--------------------------
select parsename(replace(col,'-','.'),2) from tb
----------------结果----------------------------
/*
--------------------------------------------------------------------------------------------------------------------------------
cccc
wqadf9
99134
的(4 行受影响)
 
*/

Answer 10

create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
--实现split功能 的函数
--date    :2003-10-14
as 
begin
    declare @i int
    set @SourceSql=rtrim(ltrim(@SourceSql))
    set @i=charindex(@StrSeprate,@SourceSql)
    while @i>=1
    begin
        insert @temp values(left(@SourceSql,@i-1))
        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
        set @i=charindex(@StrSeprate,@SourceSql)
    end
    if @SourceSql<>'' 
       insert @temp values(@SourceSql)
    return 
endselect * from dbo.f_split('1,2,3,4',',')a                                                                                                    
-------------------- 
1
2
3
4（所影响的行数为 4 行）