在企业管理器中,反复执行这句update test set name=mid(name,2,100) where charindex('#',name)>0直到提示“0行被影响”就好了:)
declare @t table([id] int,[name] varchar(100)) insert into @t select 1, '#a #b #c' union all select 2, '#e #d' union all select 3, '#f #g #x #b' select top 100 identity(int,1,1) ID into # from syscolumns a,syscolumns bselect a.[id] ,max(substring(a.[name],b.ID,charindex('#',a.[name]+'#',b.ID)-b.ID)) as [name] from @t a join # b on substring('#'+a.[name],b.ID,1)='#' group by a.[id]drop table #
id name
1 #a #b #c
2 #e #d
3 #f #g #x #b
要得到的查询结果为
id name
1 c
2 e
3 x
我也不知道不好判断要不就不会来请教各位了啊.
insert into @t
select 1, '#a #b #c'
union all select 2, '#e #d'
union all select 3, '#f #g #x #b'
select top 100 identity(int,1,1) ID into # from syscolumns a,syscolumns bselect a.[id]
,max(substring(a.[name],b.ID,charindex('#',a.[name]+'#',b.ID)-b.ID)) as [name]
from @t a
join # b on substring('#'+a.[name],b.ID,1)='#'
group by a.[id]drop table #