如题：在一张表中有一个deptcode字段，格式为：http://xxx.xxxxx.com.cn/SZ374.html，现在我要把SZ374拿出来跟另外一张表里面的dept_code，格式为：SZ374比较是否相等，但是因为SZ374这部分是经常变化的，我怎样才能就是说把/后面到.html前面的部分提取呢？sql语句怎么写？注不需要存储过程，谢谢！！
------解决方案--------------------
- SQL code
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) godeclare @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 @tableselect 'http://xxx.xxxxx.com.cn/SZ374.html' union allselect 'http://xxx.xxxxx.com.cn/SZ373.html' union allselect 'http://xxx.xxxxx.com.cn/abd44.html' union allselect 'http://xxx.xxxxx.com.cn/tsss.html'SELECT SUBSTRING(deptcode,LEN(deptcode)-CHARINDEX('/',REVERSE(deptcode))+2,CHARINDEX('/',REVERSE(deptcode))-CHARINDEX('.',REVERSE(deptcode))-1) AS newcolfrom @table/*newcol----------------------------------SZ374SZ373abd44tsss*/
------解决方案--------------------
- SQL code
mysql> use c1Database changedmysql> 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(talent,'com.cn/')-7) from a;+---------------------------------------------------+| Mid(talent,instr(talent,'com.cn/')+7,instr(talent,'.html')-instr(talent,'com.cn/')-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