CODEWITHSUNDEEP
sql-serversubstringcharindex
jcoke
jcoke

Reputation: 1891

substring to retrieve a date between two characters

Could someone shed some light on how I could extract the date field from this string below

'NA,Planning-Completed=17-07-2019 10:38,Print-Dispatch-Date=10-02-2020 13:06,Award-Complete=NA'

Expected Output:

10-02-2020

This is what I have written so far, it seems to work sometimes but I noticed some rows are incorrect.

[Dispatch Date] = SUBSTRING(dates,CHARINDEX('Print-Dispatch-Date=',dates) + LEN('Print-Dispatch-Date='), LEN(dates) )

Upvotes: 0

Views: 181

Answers (1)

Thom A
Thom A

Reputation: 96044

The 3rd parameter for SUBSTRING is wrong. You have it as LEN(dates), which means return every character after 'Print-Dispatch-Date='; in this case that returns '10-02-2020 13:06,Award-Complete=NA'.

As your date is 10 characters long, just use 10:

SELECT dates, SUBSTRING(dates,CHARINDEX('Print-Dispatch-Date=',dates) + LEN('Print-Dispatch-Date='), 10)
FROM (VALUES('NA,Planning-Completed=17-07-2019 10:38,Print-Dispatch-Date=10-02-2020 13:06,Award-Complete=NA'))V(dates)

Upvotes: 2

Related Questions