拆分字符串

Question

有一字符串  '06G512753-08-01'  
我想拆分成  三个字符串    '06G512753'  ,    '08'    ,    '01' 
三段都是不等长的,按 '-' 拆分

Answer 1

如何将'2006-01-12|2006-02-13|2006-03-15|2006-11-11|2003-1-1'按'|'分割成
2006-01-12
2006-02-13
2006-03-15
2006-11-11
2003-1-1
declare @Days varchar(4000)
declare @tmpDay varchar(10)
set @Days='2006-01-12|2006-02-13|2006-03-15|2006-11-11|2003-1-1'
set @tmpDay=''
declare @i int
set @i=0
while @i<len(@Days)
begin 
  set @i=@i+1
  if SUBSTRING(@Days,@i,1)='|'
     begin
       print left(@Days,@i-1)
       set @Days=SUBSTRING(@Days,@i+1,len(@Days))
       set @i=0
     end  
end
print @Days
输出结果：
2006-01-12
2006-02-13
2006-03-15
2006-11-11
2003-1-1

Answer 2

declare @s varchar(100)
set @s='06G512753-08-01'  
select parsename(t.s,3),parsename(t.s,2),parsename(t.s,1) from (select replace(@s,'-','.') as s) t

Answer 3

declare @Days varchar(4000)
declare @tmpDay varchar(10)
set @Days='06G512753-08-01'
set @tmpDay=''
declare @i int
set @i=0
while @i<len(@Days)
begin 
  set @i=@i+1
  if SUBSTRING(@Days,@i,1)='-'
     begin
       print left(@Days,@i-1)
       set @Days=SUBSTRING(@Days,@i+1,len(@Days))
       set @i=0
     end  
end
print @Days
06G512753
08
01

Answer 4

declare @s varchar(20)
set @s='06G512753-08-01'  
select 
    parsename(t.s,3) part1,
    parsename(t.s,2) part2,
    parsename(t.s,1) part3
from 
    (select replace(@s,'-','.') as s) tpart1      part2      part3      
---------- ---------- ---------- 
06G512753  08         01

Answer 5

declare @str varchar(1000),@str1 varchar(100),@str2 varchar(100)
set @str='06G512753-08-01'
print @str
set @str1=left(@str,charindex('-',@str)-1)
print @str1
set @str=right(@str,len(@str)-charindex('-',@str))
set @str2=left(@str,charindex('-',@str)-1)
print @str2
set @str=right(@str,len(@str)-charindex('-',@str))
print @str

Answer 6

Declare @S Varchar(8000)
Select @S =  '06G512753-08-01'  
Select
Left(@S, CharIndex('-', @S) -1),
Substring(@S, CharIndex('-', @S) + 1, CharIndex('-', @S, CharIndex('-', @S) + 1) - CharIndex('-', @S) -1),
Stuff(@S, 1, CharIndex('-', @S, CharIndex('-', @S) + 1), '')

Answer 7

Declare @S Varchar(8000)
Select @S =  '06G512753-08-01'  
Select
Left(@S, CharIndex('-', @S) -1),
Substring(@S, CharIndex('-', @S) + 1, CharIndex('-', @S, CharIndex('-', @S) + 1) - CharIndex('-', @S) -1),
Stuff(@S, 1, CharIndex('-', @S, CharIndex('-', @S) + 1), '')--Result
/*
06G512753 08 01
*/

Answer 8

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--测试
declare @a varchar(50)
set @a='06G512753-08-01'
select col1=dbo.f_GetStr(@a,1,'-'),col2=dbo.f_GetStr(@a,2,'-'),col3=dbo.f_GetStr(@a,2,'-')col1                                                                                                 col2                                                                                                 col3
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
06G512753                                                                                            08                                                                                                   08(1 行受影响)

Answer 9

declare @a varchar(50)
set @a='06G512753-08-01'
select col1=dbo.f_GetStr(@a,1,'-'),col2=dbo.f_GetStr(@a,2,'-'),col3=dbo.f_GetStr(@a,3,'-')
col1                                                                                                 col2                                                                                                 col3
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
06G512753                                                                                            08                                                                                                   01(1 行受影响)

Answer 10

declare @str varchar(8000)
declare @f varchar(1),@c int
set @f='-'
set @str='06G512753-08-01'
declare @t table(s varchar(32))
set @str=@str+@f
while charindex(@f,@str) > 0
begin
insert into @t select left(@str,charindex(@f,@str)-1)
set @str=right(@str,len(@str)-charindex(@f,@str))
end
select * from @t

Answer 11

declare @istr varchar(8000)
select @istr='2006-01-12|2006-02-13|2006-03-15|2006-11-11|2003-1-1'
select @istr='print '''+replace(@istr,'|',char(13))+''''--print @istrexec (@istr)

Answer 12

declare @istr varchar(8000)
select @istr='2006-01-12|2006-02-13|2006-03-15|2006-11-11|2003-1-1'
select @istr='print '''+replace(@istr,'|',char(13))+''''--print @istrexec (@istr)2006-01-12
2006-02-13
2006-03-15
2006-11-11
2003-1-1