您正在使用字符串拆分函数,该函数将项目作为行返回。您需要一个将它们作为列返回的函数。
或者您可以直接在查询中执行此操作。也许是这样的。
declare @S varchar(10) = 'AA~B~123'
select left(@S, T1.Pos - 1) as Col1,
substring(@S, T1.Pos+1, T2.Pos-T1.Pos-1) as Col2,
substring(@S, T2.Pos+1, len(@S)-T2.Pos) as Col3
from (select charindex('~', @S)) as T1(Pos)
cross apply (select charindex('~', @S, T1.Pos+1)) as T2(Pos)
Result:
Col1 Col2 Col3
---------- ---------- ----------
AA B 123
这是在 SQL Server 2000 中运行的版本
declare @S varchar(10)
set @S = 'AA~B~123'
select left(@S, T.Pos1 - 1) as Col1,
substring(@S, T.Pos1+1, T.Pos2-T.Pos1-1) as Col2,
substring(@S, T.Pos2+1, len(@S)-T.Pos2) as Col3
from (select T.Pos1,
charindex('~', @S, T.Pos1+1) as Pos2
from (select charindex('~', @S) as Pos1) as T
) as T