直接select出来
declare @string varchar(1000)
set @string='aaaa,bbbb,cccc,dddd'
set @string='select '''+replace(@string,',','''as str union all select ''')+''''
exec(@string)
DECLARE @sStr varchar(100)
SET @sStr = 'aaaa,sadfas,asdfgasdf,asdfasd,asdf,asfasdf'
DECLARE @iPosBegin int
DECLARE @iPosEnd int
DECLARE @sTemp varchar(100)
CREATE TABLE #MyResult1
(ResultStr varchar(100))
DECLARE @sResult varchar(100)
SET @iPosBegin = 0
SET @iPosBegin = PATINDEX('%,%',@sStr)
IF @iPosBegin = 0
BEGIN
PRINT 'NULL'
RETURN
END
ELSE
BEGIN
PRINT @iPosBegin
SET @sResult = SUBSTRING(@sStr,1,@iPosBegin - 1)
INSERT INTO #MyResult1
VALUES (@sResult)
SET @sStr = SUBSTRING(@sStr,@iPosBegin + 1,LEN(@sStr) - @iPosBegin )
SET @iPosBegin = PATINDEX('%,%',@sStr)
WHILE @iPosBegin > 0
BEGIN
SET @sResult = SUBSTRING(@sStr,1,@iPosBegin - 1)
INSERT INTO #MyResult1
VALUES (@sResult)
SET @sStr = SUBSTRING(@sStr,@iPosBegin + 1,LEN(@sStr) - @iPosBegin )
SET @iPosBegin = PATINDEX('%,%',@sStr)
END
直接select出来
declare @string varchar(1000)
set @string='aaaa,bbbb,cccc,dddd'
set @string='select '''+replace(@string,',','''as str union all select ''')+''''
exec(@string)
BEGIN
--------------------------------------------------------
--本地变量
--------------------------------------------------------
DECLARE @Index INT
DECLARE @Start INT
DECLARE @DelimiterSize INT
--------------------------------------------------------
--分隔符的长度
--------------------------------------------------------
SET @DelimiterSize = LEN(@Delimiter)
--------------------------------------------------------
--遍历被分隔字符串,将每个分隔单元添加到表
--------------------------------------------------------
WHILE LEN(@DelimitedString) > 0
BEGIN
SET @Index = CHARINDEX(@Delimiter, @DelimitedString)
--------------------------------------------------------
--如果只有一个单独的分隔单元,即并没有分隔标志出现在被分割字符串
--------------------------------------------------------
IF @Index = 0
BEGIN
INSERT INTO @Array VALUES(@DelimitedString)
BREAK
END
--------------------------------------------------------
--如果有多个分隔单元
--------------------------------------------------------
ELSE
BEGIN
INSERT INTO @Array VALUES(SUBSTRING(@DelimitedString, 1,@Index - 1))
SET @Start = @Index + @DelimiterSize
SET @DelimitedString = SUBSTRING(@DelimitedString, @Start , LEN(@DelimitedString) - @Start + 1)
END
END
类似如下,具体自己实现
declare @aa varchar(100)
set @aa = 'aaaa,sadfas,asdfgasdf,asdfasd,asdf,asfasdf'
while charindex(',',@aa) > 0
begin
Select left(@aa,charindex(',',@aa)-1)
Set @aa = right(@aa,len(@aa) - charindex(',',@aa))
end
Select @aa