T-sql substring function to extract specific string

Question

I am trying to extract the date in @value variable as below.

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

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

But the gotcha is that '[email protected]' is variable, there by shifting the start index from 43

Is there a better way to do this, i would prefer a way where start position of substring will start from last index of @value, and count back.

I really would consider using an ISO-8601 date format to make sure each date and time length is standardised an reconginsed independent of local setting. I'm British and your date/time format is frankly nonsense — gbn
– gbn, Commented Dec 5, 2014 at 13:26

gbn · Accepted Answer · 2014-12-05 13:24:18Z

2

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)

Now boys and girls, a solution for all date formats as well as variable length suffixes

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

edited Dec 5, 2014 at 13:24

answered Dec 5, 2014 at 13:02

gbn

434k84 gold badges602 silver badges689 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Edwin O. Over a year ago

THANKS GBN, THAT WORKED ! VOTING THIS UP!

DavidG Over a year ago

This doesn't work. What if the date is 12/12/2004?

DavidG Over a year ago

Or even 1/1/2004 or could the year be double digit?

DavidG Over a year ago

@OKonkwoIkechukwuEdwin Check my answer, it copes with other formats.

gbn Over a year ago

@DavidG: The original substring length needs to be 20 or 21 or 22 as a consequence of not using ISO-8601 dates. I've updated the solution as a T-SQL exercise

DavidG · Accepted Answer · 2014-12-05 13:09:30Z

1

Use CHARINDEX to find the string on, then use that as the start point, then do the same to remove the text after from:

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

answered Dec 5, 2014 at 13:09

DavidG

120k13 gold badges232 silver badges239 bronze badges

3 Comments

gbn Over a year ago

This also deals with times too

DavidG Over a year ago

@gbn Indeed, I replicated the results from the OPs original query.

gbn Over a year ago

Easy to fix though, just ignore it.

Collectives™ on Stack Overflow

T-sql substring function to extract specific string

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related