PATINDEX to retrieve date from varchar/memo data field

larrybye
larrybye used Ask the Experts™
on
I need to retrieve the date from a varchar/memo field, it could be formatted either mm/dd/yy or mm/dd/yyyy. I’d like to use PATINDEX as part of a function but unable to get the “pattern” correct for the date.

Data row Examples:
Qualify the unit for sale in the European community.  CANCELLED 5/14/97 See test AG20 on ED150.
All components are the same as the ED 150 5/16/1997.




Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
You're going to struggle here because PATINDEX (as far as I am aware) can not cope with optional search criteria.

i.e. dateformat 5/14/97 can be found with
      SELECT PATINDEX('%[1-9]/[0-9][0-9]/[0-9][0-9]%',@text)
but, the same search string will not find 11/14/97

I think the only way to do it would be to use PATINDEX multiple times. I think this covers all options...

SELECT PATINDEX('%[1-9]/[0-9][0-9]/[0-9][0-9]%',@text)
SELECT PATINDEX('%[1-9]/[1-9]/[0-9][0-9]%',@text)
SELECT PATINDEX('%[1][0-9]/[0-9][0-9]/[0-9][0-9]%',@text)
SELECT PATINDEX('%[1][0-9]/[1-9]/[0-9][0-9]%',@text)
SELECT PATINDEX('%[1-9]/[0-9][0-9]/[12][0-9][0-9][0-9]%',@text)
SELECT PATINDEX('%[1-9]/[1-9]/[12][0-9][0-9][0-9]%',@text)
SELECT PATINDEX('%[1][0-9]/[0-9][0-9]/[12][0-9][0-9][0-9]%',@text)
SELECT PATINDEX('%[1][0-9]/[1-9]/[12][0-9][0-9][0-9]%',@text)

Author

Commented:
Thanks, that is giving me the stating positions of the date in the field but I still need to return the actual date. I was hoping that I could modify the following function which returns numbers and “/” with a correct pattern and it would display the date but it returns the entire field.

create function dbo.fnNumbersFromStr(@str varchar(8000))
returns varchar(8000)
as
begin
      while patindex('%[^0-9/]%',@str)>0
            Set @str = rtrim(ltrim(replace(@str,substring(@str,patindex('%[^0-9/]%',@str),1),'')))
      return @str
end
go


What you’ve given me has gotten me closer, thanks again.

Commented:
Do you want to just return the first date or all of them? If all of them, should they be comma delimited or..?

Author

Commented:
Just the first date

Commented:
This should do it - I haven't tested it so it may need a little tweaking to get it right

create function dbo.fnNumbersFromStr(@str varchar(8000))
returns varchar(8000)
as
begin

      DECLARE @pos INT
      DECLARE @firstpos INT
      DECLARE @strlen INT

      SET @firstpos=PATINDEX('%[1-9]/[0-9][0-9]/[0-9][0-9]%',@str)
      SET @endpos=@firstpos+7

      SET @pos=PATINDEX('%[1-9]/[1-9]/[0-9][0-9]%',@str)
      IF @pos<@firstpos
            BEGIN
                  SET @firstpos=@pos
                  SET @strlen=6
            END

      SET @pos=PATINDEX('%[1][0-9]/[0-9][0-9]/[0-9][0-9]%',@str)
      IF @pos<@firstpos
            BEGIN
                  SET @firstpos=@pos
                  SET @strlen=8
            END

      SET @pos=PATINDEX('%[1][0-9]/[1-9]/[0-9][0-9]%',@str)
      IF @pos<@firstpos
            BEGIN
                  SET @firstpos=@pos
                  SET @strlen=7
            END

      SET @pos=PATINDEX('%[1-9]/[0-9][0-9]/[12][0-9][0-9][0-9]%',@str)
      IF @pos<@firstpos
            BEGIN
                  SET @firstpos=@pos
                  SET @strlen=9
            END

      SET @pos=PATINDEX('%[1-9]/[1-9]/[12][0-9][0-9][0-9]%',@str)
      IF @pos<@firstpos
            BEGIN
                  SET @firstpos=@pos
                  SET @strlen=8
            END

      SET @pos=PATINDEX('%[1][0-9]/[0-9][0-9]/[12][0-9][0-9][0-9]%',@str)
      IF @pos<@firstpos
            BEGIN
                  SET @firstpos=@pos
                  SET @strlen=10
            END

      SET @pos=PATINDEX('%[1][0-9]/[1-9]/[12][0-9][0-9][0-9]%',@str)
      IF @pos<@firstpos
            BEGIN
                  SET @firstpos=@pos
                  SET @strlen=9
            END
     
      IF @firstpos>0
            RETURN substring(@str,@firstpos,@strlen)
     

end
go

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial