如何在t-sql中从硬盘读取文件名
我的硬盘中有数千张照片.所有照片文件名由 4 个参数组成:
I have thousands of photos in my hard-disk. All photo file-names consists of 4 parameter :
'7 digit number ' + '-' + '3 digit number ' + '.gif'
例如:1000091-356.gif
我想知道如何编写查询以使用文件名的 3 位数字
作为参数来更新我的表,哪个主键匹配 7 位数字
的文件名.
I'm wondering how to write a query in order to use 3 digit number
of file name as a paramer for updating my table which primary key match the 7 digit number
of the file name.
换句话说,查询的作用类似于:update myTable set col2 = 356 where col1=1000091
for all photos .
By another word a query which act like : update myTable set col2 = 356 where col1=1000091
for all photos .
推荐答案
先尝试将文件名放入表格中,(使用下面的脚本)
Try getting your file names into a table first, (using the script below)
CREATE TABLE dirList (
id int identity(1,1),
line nvarchar(1000)
)
GO;
INSERT INTO dirList (line) EXEC xp_cmdshell 'dir C:\PathToMyPhotos'
SELECT * FROM dirList;
WITH CTE AS (
SELECT
id,
SUBSTRING(line,1,17) [date],
SUBSTRING(line,18,19) sizeordir,
SUBSTRING(line,37,100) name
FROM dirList
WHERE id > (
SELECT MIN(id) FROM dirList WHERE line LIKE '%<DIR>%..%'
) AND id < (SELECT MAX(id) - 2 FROM dirList)
)
SELECT
id,
[date],
isDirectory = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 1 ELSE 0 END,
isFile = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 0 ELSE 1 END,
name
FROM cte
现在您可以使用以下方法查询您的文件名:
Now you can query your filenames using:
declare @Lookup varchar(3)
set @Lookup = '123'
select name
from dirList
where isFile = 1 and substring(name, len(name)-7,3) = @Lookup
相关文章