如何在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