sql字符串截取数字

if exists(select * from sysobjects where name='maxcol')
drop table maxcol
go
create table maxcol(
id int,
code varchar(20)
)
go
insert into maxcol values(1,'小姗-1')
insert into maxcol values(2,'小姗-2')
insert into maxcol values(3,'小姗-3')
insert into maxcol values(4,'小姗-4')
insert into maxcol values(5,'小姗-5')
insert into maxcol values(6,'小姗-6')
insert into maxcol values(7,'小姗-7')
insert into maxcol values(8,'小姗-8')
insert into maxcol values(9,'小姗-9')
insert into maxcol values(10,'小姗-10')
insert into maxcol values(11,'小姗-12')
insert into maxcol values(12,'小姗-11')
insert into maxcol values(13,'小姗-13')
insert into maxcol values(14,'小姗-15')
insert into maxcol values(15,'小姗-14')
insert into maxcol values(16,'小姗-17')
insert into maxcol values(17,'小姗-16')
insert into maxcol values(18,'小姗-20')
insert into maxcol values(19,'小姗-18')
insert into maxcol values(20,'小姗-19')
go
select *from maxcol
go
select max(code)  from maxcol
go

select   SUBSTRING ('-',0,len(code))  from maxcol--获取“-”字符
select (cast(right(code,charindex('-',reverse(code))-1) as int))  from maxcol--截取字符串中的数字，获取从‘-‘开始到结束的数字字符并且转换成数字类型
select max(cast(right(code,charindex('-',reverse(code))-1) as int))  from maxcol--获取截取后的数字最大值

select reverse(code)  from maxcol  --反转字符串

select (CONVERT(INT,STUFF(code,PATINDEX('%[^0-9]%',code),3,'')))  from maxcol --必须知道截取的字符长度