请教一个存储过程,谢谢了~
表table_1中有两列
id name内容为:
1 0L-3J
2 2DR-3J
3 2R-3J
我如何使用存储过程判断name列 如果 前2个字符为 2d或者 2R的话 ,自动在前面加上 “0L-”使得查询出来的结果为:
1 0L-3J
2 OL-2DR-3J
3 OL-2R-3J
望不吝指教谢谢!
[解决办法]
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
/*
idname
10L-3J
2OL-2DR-3J
3OL-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
/*
IDname
10L-3J
2OL-2DR-3J
3OL-2R-3J
*/