select   right('http://xxx.xxxxx.com.cn/SZ374.html',charindex('/',REVERSE('http://xxx.xxxxx.com.cn/SZ374.html'))-1)

------解决方案--------------------
SQL code
declare @a varchar(300)
set @a='http://xxx.xxxxx.com.cn/SZ374.html'
select   right(@a,charindex('/',REVERSE(@a))-1) 
go

declare @a varchar(300)
set @a='http://xxx.xxxxx.rytryrtcom.cn/SZtryrt374.html'
select   right(@a,charindex('/',REVERSE(@a))-1)

------解决方案--------------------
SQL code

declare @table table (deptcode varchar(34))
insert into @table
select 'http://xxx.xxxxx.com.cn/SZ374.html' union all
select 'http://xxx.xxxxx.com.cn/SZ373.html' union all
select 'http://xxx.xxxxx.com.cn/abd44.html' union all
select 'http://xxx.xxxxx.com.cn/tsss.html'

SELECT 
SUBSTRING(deptcode,LEN(deptcode)-CHARINDEX('/',REVERSE(deptcode))+2,
CHARINDEX('/',REVERSE(deptcode))-CHARINDEX('.',REVERSE(deptcode))-1)  AS newcol
from @table

/*
newcol
----------------------------------
SZ374
SZ373
abd44
tsss
*/

------解决方案--------------------
SQL code
mysql> use c1
Database changed
mysql> select * from a;
+----+-------------------------------------------------+
| id | Talent                                          |
+----+-------------------------------------------------+
|  1 | http://xxx.xxxxx.com.cn/SZ374.html              |
|  2 | http://xxx.xxxxx.com.cn/SZ374121212.html        |
|  3 | http://xxx.xxx.com.cn/SZ3741212121312.html      |
|  4 | http://xxx.xxxxxxxx.com.cn/SZ3741212121312.html |
+----+-------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select Mid(talent,instr(talent,'com.cn/')+7,instr(talent,'.html')-instr(t
alent,'com.cn/')-7) from a;
+-------------------------------------------
--------+
| Mid(talent,instr(talent,'com.cn/')+7,instr(talent,'.html')-instr(talent,'com.c
n/')-7) |
+-------------------------------------------
--------+
| SZ374
        |
| SZ374121212
        |
| SZ3741212121312
        |
| SZ3741212121312
        |
+-------------------------------------------
--------+
4 rows in set (0.00 sec)

mysql>

------解决方案--------------------
declare @a varchar(300)
set @a='http://xxx.xxxxx.rytryrtcom.cn/SZ374.html'

select   left(right(@a,charindex('/',REVERSE(@a))-1),charindex('.',right(@a,charindex('/',REVERSE(@a))-1))-1)

------解决方案--------------------
SQL code
select    parsename(right('http://xxx.xxxxx.com.cn/SZ374.html',PATINDEX ('%/%', REVERSE('http://xxx.xxxxx.com.cn/SZ374.html'))-1),2)

------解决方案--------------------
SQL code
select    parsename(right('http://xxx.xxxxx.com.cn/SZ374.html',charindex ('/', REVERSE('http://xxx.xxxxx.com.cn/SZ374.html'))-1),2)

------解决方案--------------------
同学，楼上的都是标准答案啊:
SQL code
SELECT newrow = 
SUBSTRING(deptcode,LEN(deptcode)-CHARINDEX('/',REVERSE(deptcode))+2,
CHARINDEX('/',REVERSE(deptcode))-CHARINDEX('.',REVERSE(deptcode))-1)
FROM table_name