-1

I have an interesting task of finding a very first Date value in a Text field. There are no identifiers, sometimes the date would come in various formats such as '01/01/2015', '1/1/2015', 1/1/15', '1/01/15', etc. Format will always be Month, Day, Year; however, it could vary from mm/dd/yyyy, mm/dd/yy, m/dd/yyyy, etc.

For example:

The rain in Spain falls mainly on 01/01/2015 and then it ends between 03/02/2015 and 04/01/2015.

I want to capture "01/01/2015", because it is a very first date occurrence in the string.

Trying to figure out the easiest way to extract the very first date occurrence, but no such luck as of yet. Any suggestions?

Tks!

ssokol91
  • 552
  • 3
  • 13
  • 25

2 Answers2

2

Ok This will work somewhat or at least its a good place to start:

you can find out more about string functions here

DECLARE @ST VARCHAR(MAX) =  'The rain in Spain falls mainly on  1/1/2015 and then it ends between 03/02/2015 and 04/01/2015'

SELECT  SUBSTRING(@ST,PATINDEX ( '%[0-9]%/%[0-9]/%[0-9]%' , @ST ),PATINDEX ('%[A-Z]%', SUBSTRING(@ST,PATINDEX ( '%[0-9]%/%[0-9]/%[0-9]%' , @ST ),LEN(@ST)))-1)

result: enter image description here

Fuzzy
  • 3,810
  • 2
  • 15
  • 33
  • Thanks. Invalid length parameter passed to the LEFT or SUBSTRING function. I will try to figure out the issue in the parsing. – ssokol91 Feb 05 '16 at 22:11
1

If you're using sql, you can use a LIKE to identify that there is a date in a string:

SELECT * FROM Table WHERE Value LIKE '% %/%/% %'

Each "%" matches one or more character. If you want to extract the first date, it might be possible with a case statement. See: TSQL CASE with if comparison in SELECT statement

Community
  • 1
  • 1
PKatona
  • 629
  • 2
  • 9
  • 19