请教 SQL Server2005中截取字符串问题?有一个字段内容如下:ASB-34A1-P033
AB-S2-D3
ABC-S32-D34
KJ-K4-ER-JK
H5-G323-FSS-G54
...要求得到如下第二个'-'符号前面的数字如下:
1
2
32
4
323
AB-S2-D3
ABC-S32-D34
KJ-K4-ER-JK
H5-G323-FSS-G54
...要求得到如下第二个'-'符号前面的数字如下:
1
2
32
4
323
--写的太无聊了
create table tb(name varchar(20))
insert into tb
select 'ASB-34A1-P033' union
select 'AB-S2-D3' union
select 'ABC-S32-D34' union
select 'H5-G323-FSS-G54'
select REVERSE(left(REVERSE(left(RIGHT(name,len(name)-CHARINDEX('-',name)),
CHARINDEX('-',RIGHT(name,len(name)-CHARINDEX('-',name)))-1)),
PATINDEX('%[^0-9]%',REVERSE(left(RIGHT(name,len(name)-CHARINDEX('-',name)),
CHARINDEX('-',RIGHT(name,len(name)-CHARINDEX('-',name)))-1)))-1))
from tb/*--------------------
32
2
1
323
create table tb(name varchar(20))
insert into tb
select 'ASB-34A1-P033' union
select 'AB-S2-D3' union
select 'ABC-S32-D34' union
select 'H5-G323-FSS-G54'
goselect substring(stuff(name,1,charindex('-',name),''),charindex('-',stuff(name,1,charindex('-',name),''))-1,1)
from tbdrop table tb/*************----
2
2
1
3(4 行受影响)当然楼主要限定-的个数为2以上的name!
from tb
where len(name)-len(replace(name,'-','')) >= 2
很强!!
但还是有一个忘了说。有可能我的字段内容还有SD2-DFG-ERE
SE3-SDH-JKK遇到这种的就只需要取为 0 , 而你的取出来的是 G, H ,另外,你的代码中。对于我有
SD#-KJ234-KLL 取不出 234, 只能取出4请再帮忙,谢谢!
我仿照的代码如下:
select REVERSE(left(REVERSE(left(RIGHT(Bookcode,len(Bookcode)-CHARINDEX('-',Bookcode)),
CHARINDEX('-',RIGHT(Bookcode,len(Bookcode)-CHARINDEX('-',Bookcode)))-1)),
PATINDEX('%[^0-9]%',REVERSE(left(RIGHT(Bookcode,len(Bookcode)-CHARINDEX('-',Bookcode)),
CHARINDEX('-',RIGHT(Bookcode,len(mouldbarcode)-CHARINDEX('-',Bookcode)))-1)))-1))
from Book where createddate>='2011-8-1 00:00:00' and createddate<='2011-8-11 23:59:59'但是执行时却出错如下:
消息 536,级别 16,状态 5,第 1 行
传递到 SUBSTRING 函数的长度参数无效。
在from tablename 后面加入 where len(Bookcode)-len(replace(Bookcode,'-','')) >= 2