SQL裡,將字串欄位轉為Table的方法:
/* -- 傳入參數 -- */
declare @list nvarchar(4000)
declare @delimiter nchar(1)
set @list=@companyAll -- 將@companyAll 改為待處理字串
set @delimiter='㊣' -- 將
/* -- END -- */
create table #tmp ( listpos int IDENTITY(1, 1) NOT NULL,str varchar(4000),nstr nvarchar(2000))
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2 BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen
SET @pos = charindex(@delimiter, @tmpstr)
WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT #tmp (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END
SET @leftover = @tmpstr
END
INSERT #tmp(str, nstr)
VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
/* 刪除空白資料筆數 */
delete #tmp where len([str])=0
select * from #tmp
/* 清除暫存 table */
drop table #tmp
PS:為何要用㊣來當作區隔,因為在我的現實案例中,逗號已經無法當作唯一辨識了,所以才會選擇㊣。當然,可以根據實際情況來選擇所要區隔的符號。
沒有留言:
張貼留言