--够麻烦的,看看charindex,substring
create table tb(ID varchar(10),NUM varchar(20))
insert into tb values('213', '213-AK25-JP91')
insert into tb values('213', '213-BK25-JP91')
insert into tb values('213', '213-CK25-JP91')
goselect id , num = left(num,charindex('-',num)-1) + '-' +
case left(substring(num , charindex('-',num) + 1 , charindex('-' , num , charindex('-' , num) + 1) - charindex('-' , num) - 1),1)
when 'A' then 'G'
when 'B' then 'H'
when 'C' then 'Z'
end +
substring(substring(num , charindex('-',num) + 1 , charindex('-' , num , charindex('-' , num) + 1) - charindex('-' , num) - 1) , 2, 1) +
cast(cast(substring(substring(num , charindex('-',num) + 1 , charindex('-' , num , charindex('-' , num) + 1) - charindex('-' , num) - 1) , 3, 2) as int) + 1 as varchar) + '-' +
reverse(left(reverse(num),charindex('-',reverse(num)) - 1))
from tbdrop table tb/*
id num
---------- -------------
213 213-GK26-JP91
213 213-HK26-JP91
213 213-ZK26-JP91(3 行受影响)
*/
create table tb(ID varchar(10),NUM varchar(20))
insert into tb values('213', '213-AK25-JP91')
insert into tb values('213', '213-BK25-JP91')
insert into tb values('213', '213-CK25-JP91')
goselect id , num = left(num,charindex('-',num)-1) + '-' +
case left(substring(num , charindex('-',num) + 1 , charindex('-' , num , charindex('-' , num) + 1) - charindex('-' , num) - 1),1)
when 'A' then 'G'
when 'B' then 'H'
when 'C' then 'Z'
end +
substring(substring(num , charindex('-',num) + 1 , charindex('-' , num , charindex('-' , num) + 1) - charindex('-' , num) - 1) , 2, 1) +
cast(cast(substring(substring(num , charindex('-',num) + 1 , charindex('-' , num , charindex('-' , num) + 1) - charindex('-' , num) - 1) , 3, 2) as int) + 1 as varchar) + '-' +
reverse(left(reverse(num),charindex('-',reverse(num)) - 1))
from tbdrop table tb/*
id num
---------- -------------
213 213-GK26-JP91
213 213-HK26-JP91
213 213-ZK26-JP91(3 行受影响)
*/
解决方案 »
- 关于sql server 2005存储过程解密的问题,
- SQL 2000 日期类型的数据比对
- 求基础SQL查询语句,和基本语法!
- 一个处理数据库比较难的存储过程 兄弟们给看看 :)
- Identity的属性释疑
- 求sql
- 有三个分类,a,b,c这三类,随机显示各个分类的一条记录,也就是有三条,不知如何写? select top 1 * from table where class='a' order by ne
- 大婶们帮写个SQL
- SQLSERVER2000怎么无法新建SQLSERVER注册啊,提示信息是未找到指定的SQLSERVER,connectionopen(connect)请验证SQLSERVER是否在运行,并检查
- 这样的SQL能否实现?
- 关于在一个存储过程中调用另外四个存储过程的问题
- 请问:如何将一个字段的值,分为二种情况显示为二列。
insert @t select '213','213-BK25-JP91 'declare @tb table(col1 char(1),col2 char(1))
insert @tb select 'A','G'
insert @tb select 'B','H'
insert @tb select 'C','Z'update a
set num = left(num,4)+(select col2 from @tb where col1 = substring(a.num,5,1))+substring(num,6,1)
+right('00'+ltrim(cast(substring(num,7,2) as int )+1),2)+substring(num,9,5)
from @t a
select * from @t
/*
ID NUM
---- --------------------
213 213-HK26-JP91(所影响的行数为 1 行)
*/
insert into tb values('213', '213-AK25-JP91')
insert into tb values('213', '213-BK25-JP91')
insert into tb values('213', '213-CK25-JP91')
goselect id , num = left(num,4) +
case substring(num,5,1) when 'A' then 'G' when 'B' then 'H' when 'C' then 'Z' end +
substring(num,6,1) +
cast(cast(substring(num,7,2) as int) + 1 as varchar) +
substring(num,9,5)
from tbdrop table tb
/*
id num
---------- ---------------------------------------------------
213 213-GK26-JP91
213 213-HK26-JP91
213 213-ZK26-JP91(3 行受影响)
*/
insert @t select '213','213-BK25-JP91 'declare @tb table(col1 char(1),col2 char(1))
insert @tb select 'A','G'
insert @tb select 'B','H'
insert @tb select 'C','Z'update t
set num=stuff(Num,5,1,(select top 1 col2 from @Tb where col1=substring(t.num,5,1)))
from
@T tselect * from @t
ID NUM
---- --------------------
213 213-HK25-JP91 (所影响的行数为 1 行)
213 213-HK25-JP91213 213-HK26-JP91
insert @t select '213','213-BK25-JP91 'declare @tb table(col1 char(1),col2 char(1))
insert @tb select 'A','G'
insert @tb select 'B','H'
insert @tb select 'C','Z'update t
set num=stuff(Num,5,4,(select top 1 col2 from @Tb where col1=substring(t.num,5,1))+substring(t.num,6,1)+rtrim(substring(t.num,7,2)+1))
from
@T tselect * from @t
ID NUM
---- --------------------
213 213-HK26-JP91 (所影响的行数为 1 行)
之能保证两个“-"之间的字符串是以字母开头,数字结尾。
麻烦各位了,祝圣诞快乐!