How to use substring to put the date from these numbers

Question

how to pull the date (20060807) of these numbers

1.2.840.113782.1.3.5.8696.41870.20060807.69548508               
1.2.840.113782.1.3.1.JDI.65.1.2002816.205431857 
1.2.840.113782.1.3.1.JDI.06.8.2002816.19213160
1.2.840.113782.1.3.5.2360.28594.20030826.80612275 
1.2.840.113782.1.3.1.JDI.35.26.2002816.207943

no, all these numbers are stored as varchar. these two have diff formats (2002816, 20030826) — Jason312, Feb 03 '18 at 21:02
basically i want to pull the information before the last dot — Jason312, Feb 03 '18 at 21:04

score 1 · Accepted Answer · answered Feb 03 '18 at 21:01

Let me assume that the date formats are consistent. If so, you can do:

select substring(col, len(col) - charindex('.', reverse(col)) - 7, 8)

Because the date formats are not consistent, you might end up with an extra '.' at the end. So, get rid of it using replace():

select replace(substring(col, len(col) - charindex('.', reverse(col)) - 7, 8), '.', '')

Here is a SQL Fiddle.

How to use substring to put the date from these numbers

1 Answers1