T-sql子字符串函数提取特定的字符串

Question

我想在下面提取@value变量中的日期。

Declare @value nvarchar(61)= 'Generated by [email protected] on 12/5/2014 1:00:13 PM from ATOM.' 

Select SUBSTRING(@value,43,len(@value)) 

但小问题，就是“[email protected]”是可变的，有来自43

位移开始指数有没有更好的方式来做到这一点，我宁愿方式从哪里开始子串的位置将从@value的最后一个索引开始，并计数回来。

Answer 1

Declare @value nvarchar(100)= 'Generated by [email protected] on 12/5/2014 1:00:13 PM from ATOM.' 

Select LEFT(REVERSE(LEFT(REVERSE(RTRIM(@value)), 31)),20) 

现在男孩和女孩，所有的日期格式的解决方案，以及可变长度后缀

Declare @values table (value nvarchar(100) NOT NULL); 

INSERT @values VALUES 
    ('Generated by [email protected] on 2/2/2014 1:00:13 PM from ATOM.'), 
    ('Generated by [email protected] on 17/2/2014 1:00:13 PM from SOmewhere.'), 
    ('Generated by [email protected] on 2/11/2014 1:00:13 PM from NeverNeverLand.'), 
    ('Generated by [email protected] on 12/11/2014 1:00:13 PM from X.') 


SELECT 
    RTRIM(SUBSTRING(V.value, PATINDEX(X.Pattern, V.value)+1, X.Patlength)) 
FROM 
    @values V 
    JOIN 
    (
    VALUES 
     ('% [1-9]/[1-9]/2[0-3][0-9][0-9]%', 20), 
     ('% [1-3][0-9]/[1-9]/2[0-3][0-9][0-9]%', 21), 
     ('% [1-9]/1[0-2]/2[0-3][0-9][0-9]%', 21), 
     ('% [1-3][0-9]/1[0-2]/2[0-][0-9][0-9]%', 22) 
    ) X (Pattern, Patlength) ON PATINDEX(X.Pattern, V.value) > 0 

Answer 2

使用CHARINDEX查找的字符串on，然后使用它作为起点，然后做同样from后删除文本：

Select SUBSTRING(@value,CHARINDEX(' on ', @value)+4,CHARINDEX(' from ', SUBSTRING(@value,CHARINDEX(' on ', @value)+4,len(@value))))