Search code examples
sqlt-sqlsubstringssms-2012patindex

How to use substring to put the date from these numbers

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
Solution

  • 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.