TSQL:在字符串中查找具有不同字符的日期 [英] TSQL: Find a date with varying characters in a string

问题描述

我需要从列名文件名的字符串中找到一个连续的日期.字符串中还有其他数字和破折号(或其他字符，如下划线)，但我只需要连续数字

需要从文件名中提取日期.(我知道数据只是哇，多个供应商，多种文件命名格式是原因.)

这个问题与这个问题相似，但它寻找不同的要求不同的东西:

实际结果:

测试代码:

删除表#dob创建表#dob (文件名 VARCHAR(MAX),学生证 INT)插入#dob( 文档名称  )价值观('Smith John D, 11-23-1980, 1234567.pdf'),('Doe Jane, _01_22_1980_123456.pdf'),('John Doe, 567891.pdf' )——这是我试过的.选择文件名, substring(FileName, patindex('%[0-9][%-%][%_%][0-9][0-9][0-9][0-9][0-9]%', 文件名), 8) AS dob来自#dob

解决方案

我认为您的模式不太正确.此外，您可以使用 CASE 表达式返回 NULL:

选择文件名，(当文件名 LIKE '%[0-9][0-9][-_][0-9][0-9][-_][0-9][0-9][0-9][0-9]%'THEN substring(FileName, patindex('%[0-9][0-9][-_][0-9][0-9][-_][0-9][0-9][0-9][0-9]%', 文件名), 10)END) AS dob从#dob;

你也可以不用CASE而使用NULLIF():

 substring(FileName, NULLIF(patindex('%[0-9][0-9][-_][0-9][0-9][-_][0-9][0-9][0-9][0-9]%', FileName), 0), 10) as dob

I need to find a continuous date in a string from column name Filename. The string has other numbers in it with dashes(or another character, like an underscore), but I only need the continuous number

The Date needs to be extracted from the filename. (I know the data is just wow, multiple vendors, multiple file naming formats is the cause.)

This question is similar to this question, but it's looking for something different with a different requirement: TSQL: Find a continuous number in a string

Desired result:

Actual Result:

Test Code:

DROP TABLE #dob

CREATE TABLE #dob (
 FILENAME VARCHAR(MAX)
,StudentID INT
)

INSERT INTO #dob
( FILENAME  )
VALUES
 ('Smith John D, 11-23-1980, 1234567.pdf')
,('Doe Jane, _01_22_1980_123456.pdf')
,('John Doe, 567891.pdf' )

--This is what I tried.

SELECT FILENAME
, substring(FileName, patindex('%[0-9][%-%][%_%][0-9][0-9][0-9][0-9][0-9]%', FileName), 8) AS dob
FROM #dob

解决方案

I don't think you have the pattern quite right. Also, you can use a CASE expression to return NULL:

SELECT FILENAME,
       (CASE WHEN FileName LIKE '%[0-9][0-9][-_][0-9][0-9][-_][0-9][0-9][0-9][0-9]%'
             THEN substring(FileName, patindex('%[0-9][0-9][-_][0-9][0-9][-_][0-9][0-9][0-9][0-9]%', FileName), 10)
        END) AS dob
FROM #dob;

You can also dispense with the CASE and use NULLIF():

   substring(FileName, NULLIF(patindex('%[0-9][0-9][-_][0-9][0-9][-_][0-9][0-9][0-9][0-9]%', FileName), 0), 10) as dob

这篇关于TSQL:在字符串中查找具有不同字符的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！