ID Name
1 CPCD160-CU2
2 CPCD120-WX
3 CPCD160-CU2
4 CPCD25-WS1
5 CPCD25-WS1
6 CPCDCE100-WS1现在要截取name中间的数字,如:
Name
160
120
160
25
25
100
这样的sql语句该怎么写啊
1 CPCD160-CU2
2 CPCD120-WX
3 CPCD160-CU2
4 CPCD25-WS1
5 CPCD25-WS1
6 CPCDCE100-WS1现在要截取name中间的数字,如:
Name
160
120
160
25
25
100
这样的sql语句该怎么写啊
if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] int,[Name] varchar(13))
insert [TB]
select 1,'CPCD160-CU2' union all
select 2,'CPCD120-WX' union all
select 3,'CPCD160-CU2' union all
select 4,'CPCD25-WS1' union all
select 5,'CPCD25-WS1' union all
select 6,'CPCDCE100-WS1'select
substring([name],patindex('%[0-9]%',[name]),charindex('-',[name])-patindex('%[0-9]%',[name]))
from [TB]/*-------------
160
120
160
25
25
100(6 行受影响)*/
drop table [TB]