--> 测试数据:# if object_id('tempdb.dbo.#') is not null drop table # create table #(test varchar(46)) insert into # select 'C:\abc\s.smil,C:\def\33661600.jpg,X:\xx\xx.bmp'select jpg = reverse(substring(reverse(test), charindex('\', reverse(test), charindex('gpj.',reverse(test))+3), charindex(':', reverse(test), charindex('gpj.',reverse(test))+3)-charindex('\', reverse(test), charindex('gpj.',reverse(test))+3)+2)) from # where test like '%.jpg%'/* jpg ---------------------------------------------- C:\def\ */
要不要文件名随你:--> 测试数据:# if object_id('tempdb.dbo.#') is not null drop table # create table #(test varchar(46)) insert into # select 'C:\abc\s.smil,C:\def\33661600.jpg,X:\xx\xx.bmp'select jpg = reverse(substring(reverse(test), charindex('\', reverse(test), charindex('gpj.',reverse(test))+3), charindex(':', reverse(test), charindex('gpj.',reverse(test))+3)-charindex('\', reverse(test), charindex('gpj.',reverse(test))+3)+2)) from # where test like '%.jpg%' /* jpg ---------------------------------------------- C:\def\ */select jpg = reverse(substring(reverse(test), charindex('gpj.',reverse(test)), charindex(':', reverse(test), charindex('gpj.',reverse(test))+3)-charindex('gpj.',reverse(test))+2)) from # where test like '%.jpg%' /* jpg ---------------------------------------------- C:\def\33661600.jpg */
http://topic.csdn.net/u/20101019/11/38c453d7-7d60-41a1-ac92-b72a18942d89.html
test='C:\Program Files\MMSserver\Mms-in\080916441392000601237\s.smil,C:\Program Files\MMSserver\Mms-in\080916441392000601237\33661600.jpg'
这个是最少路径的时候
if object_id('tempdb.dbo.#') is not null drop table #
create table #(test varchar(46))
insert into #
select 'C:\abc\s.smil,C:\def\33661600.jpg,X:\xx\xx.bmp'select jpg = reverse(substring(reverse(test), charindex('\', reverse(test), charindex('gpj.',reverse(test))+3), charindex(':', reverse(test), charindex('gpj.',reverse(test))+3)-charindex('\', reverse(test), charindex('gpj.',reverse(test))+3)+2)) from # where test like '%.jpg%'/*
jpg
----------------------------------------------
C:\def\
*/
SELECT REVERSE(SUBSTRING(REVERSE(@Var), CHARINDEX('gpj.', REVERSE(@Var)),
CHARINDEX(':C',
SUBSTRING(REVERSE(@Var),
CHARINDEX('gpj.', REVERSE(@Var)),
LEN(REVERSE(@Var)))) + 1))--C:\Program Files\MMSserver\Mms-in\080916441392000601237\33661600.jpg
if object_id('tempdb.dbo.#') is not null drop table #
create table #(test varchar(46))
insert into #
select 'C:\abc\s.smil,C:\def\33661600.jpg,X:\xx\xx.bmp'select jpg = reverse(substring(reverse(test), charindex('\', reverse(test), charindex('gpj.',reverse(test))+3), charindex(':', reverse(test), charindex('gpj.',reverse(test))+3)-charindex('\', reverse(test), charindex('gpj.',reverse(test))+3)+2)) from # where test like '%.jpg%'
/*
jpg
----------------------------------------------
C:\def\
*/select jpg = reverse(substring(reverse(test), charindex('gpj.',reverse(test)), charindex(':', reverse(test), charindex('gpj.',reverse(test))+3)-charindex('gpj.',reverse(test))+2)) from # where test like '%.jpg%'
/*
jpg
----------------------------------------------
C:\def\33661600.jpg
*/