求SQL语句

[obuntu]

declare @str varchar(20)

set @str='aa;bbdfa;fadfa;'

select CHARINDEX(';',@str)

select SUBSTRING(@str,0,CHARINDEX(';',@str))

现在想把@str里的值以分号为间隔，依次读出来，要咋整？

[lidanzi]

declare @str varchar(20)

set @str='aa;bbdfa;fadfa;'

select parsename(left(replace(@str,';','.'),len(replace(@str,';','.'))-1),3),
parsename(left(replace(@str,';','.'),len(replace(@str,';','.'))-1),2),
parsename(left(replace(@str,';','.'),len(replace(@str,';','.'))-1),1)

[tim_spac]

set nocount on
declare @str varchar(20)
set @str='aa;bbdfa;fadfa;'

declare @splited table (s varchar(20))
declare @pos int, @got varchar(20)
set @pos = charindex(';',@str)
while @pos>0 begin
    select @got = left(@str,@pos-1), @str = right(@str, len(@str)-@pos)
    insert into @splited values (@got)
    set @pos = charindex(';',@str)
end
if @str>''
    insert into @splited values (@str)


select * from @splited
-- aa
-- bbdfa
-- fadfa

[Leshami]

declare @str varchar(20)
SET   @str='aa;bbdfa;fadfa;'

SET  @str = LEFT(@str,LEN(@str)-1)

DECLARE @tb TABLE(Col VARCHAR(20))

WHILE CHARINDEX(';',@str)>0

BEGIN 
INSERT @tb  SELECT LEFT(@str,CHARINDEX(';',@str)-1)
		set  @str=STUFF(@str,1,CHARINDEX(';',@str),'')
	
END 
INSERT INTO @tb SELECT @str	
SELECT * FROM @tb

Col
--------------------
aa
bbdfa
fadfa

[SQL77]

declare @str varchar(20),@SQL varchar(100)

set @str='aa;bbdfa;fadfa;'

SELECT @SQL='SELECT COL='''+REPLACE(@STR,';',''' UNION ALL SELECT ''')+''''

EXEC( @SQL)

COL   
----- 
aa
bbdfa
fadfa

[--小F--]

不写了 ..