日期:2014-05-17 浏览次数:20584 次
select case when substring(name,1,2) in ('2d','2R') then '0L-'+name else name end from table_1
create table table_1(id int,name varchar(10))
insert into table_1
select 1,'0L-3J' union all
select 2,'2DR-3J' union all
select 3,'2R-3J'
select id,case when left(name,2) in('2D','2R') then '0L-'+name else name end 'name'
from table_1
/*
id name
----------- -------------
1 0L-3J
2 0L-2DR-3J
3 0L-2R-3J
(3 row(s) affected)
*/
一条sql语句可以处理:
;with cte(id,name) as
(
select 1,'0L-3J'
union all select 2,'2DR-3J'
union all select 3,'2R-3J'
)
select id,name=case when LEFT(name,2)='2d' Or LEFT(name,2)='2r' then 'OL-'+name else name end
from cte
/*
id name
1 0L-3J
2 OL-2DR-3J
3 OL-2R-3J
*/
create table table_1(id int,name varchar(10))
insert into table_1
select 1,'0L-3J' union all
select 2,'2DR-3J' union all
select 3,'2R-3J'
select ID,
case when name like '2d%' or name like '2r%'
then 'OL-'+name
else name end as name
from table_1