select right(CH部门,len(CH部门)-2) as 部门, right(CH单位,len(CH单位)-2) as 单位, right(CH问题,len(CH问题)_2) as 问题 .......... 姓名, 年龄 from tb可是这个意思? 最好你给出字段,数据和想要的结果. 不然不好搞.
取数据库所有带CH的字段名 select b.name from sysobjects a , syscolumns b where a.id=b.id and a.type='U' and b.name like '%CH%'
取数据库所有带CH的字段名同时去掉‘CH’ select right(b.name,len(b.name)-2) as name from sysobjects a , syscolumns b where a.id=b.id and a.type='U' and b.name like '%CH%'
如果还要显示表名,那么 select a.name as tbname,right(b.name,len(b.name)-2) as colname from sysobjects a , syscolumns b where a.id=b.id and a.type='U' and b.name like 'CH%'
用动态SQL来做吧 create procedure select_noch(@tablename varchar(100)) as declare @sql varchar(8000) set @sql='select ' select @sql=@sql+a.name+(case when a.name like 'CH%' then ' '+right(a.name,len (a.name)-2) else '' end)+',' from syscolumns a inner join sysobjects b on a.id=b.id where b.type='U' and b.name =@tablename order by colid set @sql=left(@sql,len(@sql)-1)+' from '+@tablename exec(@sql)
right(CH单位,len(CH单位)-2) as 单位,
right(CH问题,len(CH问题)_2) as 问题
..........
姓名,
年龄
from tb可是这个意思?
最好你给出字段,数据和想要的结果.
不然不好搞.
,是不是应该有别的办法
select b.name
from sysobjects a , syscolumns b
where a.id=b.id and a.type='U' and b.name like '%CH%'
取数据库所有带CH的字段名同时去掉‘CH’
select right(b.name,len(b.name)-2) as name
from sysobjects a , syscolumns b
where a.id=b.id and a.type='U' and b.name like '%CH%'
select a.name as tbname,right(b.name,len(b.name)-2) as colname
from sysobjects a , syscolumns b
where a.id=b.id and a.type='U' and b.name like 'CH%'
create procedure select_noch(@tablename varchar(100))
as
declare @sql varchar(8000)
set @sql='select '
select @sql=@sql+a.name+(case when a.name like 'CH%' then ' '+right(a.name,len
(a.name)-2) else '' end)+',' from syscolumns a inner join sysobjects b on
a.id=b.id where b.type='U' and b.name =@tablename order by colid
set @sql=left(@sql,len(@sql)-1)+' from '+@tablename
exec(@sql)