sql分割字符串

[tangserver]

例:
文件路径字段

C:\Documents and Settings\Administrator\桌面\已导入\上海\乙俊英\拜访表-SH145-0217.xls
C:\Documents and Settings\Administrator\桌面\已导入\上海\乙俊英\拜访表-SH158-0217.xls
C:\Documents and Settings\Administrator\桌面\已导入\北京\张树兴\西单拜访表-BJ254-0217.xls
C:\Documents and Settings\Administrator\桌面\已导入\成都\张强\拜访表-CD201-0216-张强.XLS
C:\Documents and Settings\Administrator\桌面\已导入\武汉\吕学军\江汉北路WH062-0217.xls
C:\Documents and Settings\Administrator\桌面\已导入\深圳\冯华豪12-14拜访表\拜访表SHZ047-02214-西丽人人乐.xls
C:\Documents and Settings\Administrator\桌面\已导入\西安\西安\拜访表-X'A051-0217.xls
C:\Documents and Settings\Administrator\桌面\已导入\成都\成都\拜访表-CD0204-0218.xls

我要从上面文件路径这个字段里提取到
SH145
SH158
BJ254
CD201
WH062
SHZ047
X'A051
CD0204

有什么办法吗?

[sdhdy]

好像不太有规律。

[lgx0914]

等待高手

[快乐_石头]

[Quote=引用 1 楼 sdhdy 的回复:]
好像不太有规律。
[/Quote]
...

[子陌红尘]

declare @t table(URL varchar(120))
insert into @t select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\上海\乙俊英\拜访表-SH145-0217.xls                       ')
insert into @t select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\上海\乙俊英\拜访表-SH158-0217.xls                       ')
insert into @t select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\北京\张树兴\西单拜访表-BJ254-0217.xls                   ')
insert into @t select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\成都\张强\拜访表-CD201-0216-张强.XLS                    ')
insert into @t select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\武汉\吕学军\江汉北路WH062-0217.xls                      ')
insert into @t select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\深圳\冯华豪12-14拜访表\拜访表SHZ047-02214-西丽人人乐.xls')
insert into @t select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\西安\西安\拜访表-X''A051-0217.xls                        ')
insert into @t select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\成都\成都\拜访表-CD0204-0218.xls                        ')

select
    LEFT(URL,CHARINDEX('-',URL)-1) as URL
from
    (select
         STUFF(URL,1,PATINDEX('%[a-z]%',URL)-1,'') as URL
     from
        (select
             REVERSE(LEFT(REVERSE(URL),CHARINDEX('\',REVERSE(URL))-1)) as URL
         from 
             @t) b ) a

/*
URL
-----------------
SH145
SH158
BJ254
CD201
WH062
SHZ047
X'A051
CD0204
*/

[pl_mm]

declare @tb table(s varchar(200))
insert into @tb select 'C:\Documents and Settings\Administrator\桌面\已导入\上海\乙俊英\拜访表-SH145-0217.xls'
insert into @tb select '--C:\Documents and Settings\Administrator\桌面\已导入\北京\张树兴\西单拜访表-BJ254-0217.xls'

select substring(
reverse(left(reverse(s),charindex('\',reverse(s))-1)),
charindex('-',reverse(left(reverse(s),charindex('\',reverse(s))-1)))+1
,len(left(reverse(s),charindex('\',reverse(s))-1))-
charindex('-',left(reverse(s),charindex('\',reverse(s))-1))
-charindex('-',reverse(left(reverse(s),charindex('\',reverse(s))-1))))
from @tb

SH145
BJ254

[sdhdy]

select substring(right(a,len(a)-charindex('-',a)),1,(charindex('-',right(a,len(a)-charindex('-',a)))-1)) from aa 
where (charindex('-',right(a,len(a)-charindex('-',a)))-1)>=1

[pl_mm]

declare @tb table(s varchar(200))
insert into @tb select 'C:\Documents and Settings\Administrator\桌面\已导入\上海\乙俊英\拜访表-SH145-0217.xls'
insert into @tb select '--C:\Documents and Settings\Administrator\桌面\已导入\北京\张树兴\西单拜访表-BJ254-0217.xls'

select substring(s,charindex('-',s)+1,len(s)-charindex('-',s)-charindex('-',reverse(s)))
from (
select s=reverse(left(reverse(s),charindex('\',reverse(s))-1))
from @tb)t

SH145
BJ254

[sdhdy]

create table aa(a varchar(1000))
go

insert aa select 'C:\Documents and Settings\Administrator\桌面\已导入\上海\乙俊英\拜访表-SH145-0217.xls' 
insert aa select 'C:\Documents and Settings\Administrator\桌面\已导入\上海\乙俊英\拜访表-SH158-0217.xls' 
insert aa select 'C:\Documents and Settings\Administrator\桌面\已导入\北京\张树兴\西单拜访表-BJ254-0217.xls' 
insert aa select 'C:\Documents and Settings\Administrator\桌面\已导入\成都\张强\拜访表-CD201-0216-张强.XLS' 
insert aa select 'C:\Documents and Settings\Administrator\桌面\已导入\武汉\吕学军\江汉北路WH062-0217.xls' 
insert aa select 'C:\Documents and Settings\Administrator\桌面\已导入\深圳\冯华豪12-14拜访表\拜访表SHZ047-02214-西丽人人乐.xls' 
insert aa select 'C:\Documents and Settings\Administrator\桌面\已导入\西安\西安\拜访表-X''A051-0217.xls' 
insert aa select 'C:\Documents and Settings\Administrator\桌面\已导入\成都\成都\拜访表-CD0204-0218.xls' 
go
select substring(right(a,len(a)-charindex('-',a)),1,(charindex('-',right(a,len(a)-charindex('-',a)))-1)) from aa 
where (charindex('-',right(a,len(a)-charindex('-',a)))-1)>=1
go
drop table aa
/*
SH145
SH158
BJ254
CD201
14拜访表\拜访表SHZ047
X'A051
CD0204*/

[pl_mm]

if object_id('tb') is not null
drop table tb
go
create table tb(s varchar(1000))
insert tb select 'C:\Documents and Settings\Administrator\桌面\已导入\上海\乙俊英\拜访表-SH145-0217.xls' 
insert tb select 'C:\Documents and Settings\Administrator\桌面\已导入\上海\乙俊英\拜访表-SH158-0217.xls' 
insert tb select 'C:\Documents and Settings\Administrator\桌面\已导入\北京\张树兴\西单拜访表-BJ254-0217.xls' 
insert tb select 'C:\Documents and Settings\Administrator\桌面\已导入\成都\张强\拜访表-CD201-0216-张强.XLS' 
insert tb select 'C:\Documents and Settings\Administrator\桌面\已导入\武汉\吕学军\江汉北路WH062-0217.xls' 
insert tb select 'C:\Documents and Settings\Administrator\桌面\已导入\深圳\冯华豪12-14拜访表\拜访表SHZ047-02214-西丽人人乐.xls' 
insert tb select 'C:\Documents and Settings\Administrator\桌面\已导入\西安\西安\拜访表-X''A051-0217.xls' 
insert tb select 'C:\Documents and Settings\Administrator\桌面\已导入\成都\成都\拜访表-CD0204-0218.xls' 

select cast(replace(cast(cast('<root><r>'+replace(s,'\','</r><r>') +'</r></root>' as xml)
.query('
for $xml in //r[last()]
return $xml
') as varchar(8000)),'-','</r><r>')
as xml).value('(//r/text())[2]','varchar(100)')
from tb

SH145
SH158
BJ254
CD201
0217.xls
02214
X'A051
CD0204

[htl258_Tony]

[Quote=引用 4 楼 libin_ftsafe 的回复:]
SQL code
declare @t table(URL varchar(120))
insert into @t select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\上海\乙俊英\拜访表-SH145-0217.xls ')
insert into @t select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\上海\乙俊英\拜访表-SH158-0217.xls ')
insert into @t select rtrim('C:\Documents and Settings\Administrator\桌…
[/Quote]顶到极致!

[tangserver]

子陌红尘(retired) 太厉害了,佩服

[肥龙上天]

create table #1 (URL varchar(120))
insert into #1 select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\上海\乙俊英\拜访表-SH145-0217.xls                       ')
insert into #1 select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\上海\乙俊英\拜访表-SH158-0217.xls                       ')
insert into #1 select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\北京\张树兴\西单拜访表-BJ254-0217.xls                   ')
insert into #1 select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\成都\张强\拜访表-CD201-0216-张强.XLS                    ')
insert into #1 select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\武汉\吕学军\江汉北路WH062-0217.xls                      ')
insert into #1 select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\深圳\冯华豪12-14拜访表\拜访表SHZ047-02214-西丽人人乐.xls')
insert into #1 select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\西安\西安\拜访表-X''A051-0217.xls                        ')
insert into #1 select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\成都\成都\拜访表-CD0204-0218.xls                        ')
insert into #1 select rtrim('C:\Documents and Settings\Administrator\桌面\已导入\成都\成都\拜访表-99D0204-0218.xls                        ')

select substring(url,patindex('%[a-zA-Z0-9]%',url),patindex('%[a-zA-Z0-9]-%',url)-patindex('%[a-zA-Z0-9]%',url)+1)
from 
(	select
	REVERSE(LEFT(REVERSE(URL),CHARINDEX('\',REVERSE(URL))-1)) as URL
	from #1
)t
------------------------------------------------------------------------------------------------------------------------
SH145
SH158
BJ254
CD201
WH062
SHZ047
X'A051
CD0204
99D0204