日期:2014-05-17 浏览次数:20646 次
declare @A table(UserInfo nvarchar(50)) insert into @A select N'小王/男/24/Depart1' union select N'小李/男/28/Depart2' union select N'小张/男/29/Depart3' declare @B table(name nvarchar(4),sex nvarchar(1),age int,department nvarchar(20)) insert into @B select name,substring(replace(UserInfo,name+'/',''),0,charindex('/',replace(UserInfo,name+'/',''))) sex, reverse(substring(reverse(replace(UserInfo,'/'+department,'')),0,charindex('/',reverse(replace(UserInfo,'/'+department,''))))) age,department from ( select substring(UserInfo,0,charindex('/',UserInfo)) name, reverse(substring(reverse(UserInfo),0,charindex('/',reverse(UserInfo)))) department,UserInfo from @A ) t select * from @B /* name sex age department ---- ---- ----------- -------------------- 小李 男 28 Depart2 小王 男 24 Depart1 小张 男 29 Depart3 */
------解决方案--------------------
写一个函数将/分隔的字符串返回为表
然后使用该函数将A表的数据转换为视图的形式,用于B或其它用途.