表的某字段FileType中保存的数据为:
FileType
D:/soft/20090301/12345.doc
D:/soft/20090302/44321.jpg
D:/soft/20090303/40110.html
…如何只取文件的后缀名
如:
FileType
doc
jpg
html
FileType
D:/soft/20090301/12345.doc
D:/soft/20090302/44321.jpg
D:/soft/20090303/40110.html
…如何只取文件的后缀名
如:
FileType
doc
jpg
html
select parsename(FileType,1) from tb
insert into tb values('D:/soft/20090301/12345.doc')
insert into tb values('D:/soft/20090302/44321.jpg')
insert into tb values('D:/soft/20090303/40110.html')
goselect reverse(left(reverse(FileType) , charindex('.',reverse(FileType)) - 1)) from tb where charindex('.',FileType) > 0drop table tb/*
--------------------------------------------------
doc
jpg
html(所影响的行数为 3 行)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([FileType] varchar(27))
insert [tb]
select 'D:/soft/20090301/12345.doc' union all
select 'D:/soft/20090302/44321.jpg' union all
select 'D:/soft/20090303/40110.html'
---查询---
select
right(filetype,charindex('.',reverse(filetype))-1) as filetype
from [tb]
---结果---
filetype
---------------------------
doc
jpg
html(所影响的行数为 3 行)
select reverse(left(reverse(FileType),charindex('.',reverse(FileType))-1)) from tb