[ztjSQL]数据库一字段为路径的处理和查找

 问题：数据库中有一个字段存放了文件全路径，我想取得文件夹路径并按文件夹group一下，不知道需要怎么写。。。

字符串处理"c:/XXXX/333.dat"如何取得"c:/XXXX/"

而后group by "c:/XXXX"

［解决方法］
declare @t table (
path varchar(20)
)

insert @t select

'c:/XXXX/333.dat'
union all select
'c:/XXXX/444.dat'
union all select
'c:/XXXX/44/4.dat'
union all select
'c:/XXXX/44/1.dat'
union all select
'c:/XXXX/44/2.dat'
union all select
'c:/XXXX/33/1.dat'

select * from @t

select path, REVERSE(path) as b from @t

select path,REVERSE(path) as b,charindex('/',REVERSE(path)) as c from @t

select left(path,len(path)-charindex('/',REVERSE(path))) as a,count(*) as cnt
from @t
group by left(path,len(path)-charindex('/',REVERSE(path)))

charindex说明
第一个代码示例返回序列"wonderful"在 titles 表的 notes 列中开始的位置。第二个示例使用可选的 start_location 参数从 notes 列的第五个字符开始寻找"wonderful"。第三个示例显示了当 expression2 内找不到 expression1 时的结果集。

USE pubs
GO
SELECT CHARINDEX('wonderful', notes)
FROM titles
WHERE title_id = 'TC3218'
GO

-- Use the optional start_location parameter to start searching
-- for wonderful starting with the fifth character in the notes
-- column.
USE pubs
GO
SELECT CHARINDEX('wonderful', notes, 5)
FROM titles
WHERE title_id = 'TC3218'
GO

下面是第一个查询和第二个查询的结果集：

-----------
46         

(1 row(s) affected)

USE pubs
GO
SELECT CHARINDEX('wondrous', notes)
FROM titles
WHERE title_id='TC3218'
GO

下面是结果集。
-----------
0         

(1 row(s) affected)