larrybye
asked on
PATINDEX to retrieve date from varchar/memo data field
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Do you want to just return the first date or all of them? If all of them, should they be comma delimited or..?
ASKER
Just the first date
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]%',@s tr)
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]%',@st r)
IF @pos<@firstpos
BEGIN
SET @firstpos=@pos
SET @strlen=9
END
IF @firstpos>0
RETURN substring(@str,@firstpos,@ strlen)
end
go
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]
SET @endpos=@firstpos+7
SET @pos=PATINDEX('%[1-9]/[1-9
IF @pos<@firstpos
BEGIN
SET @firstpos=@pos
SET @strlen=6
END
SET @pos=PATINDEX('%[1][0-9]/[
IF @pos<@firstpos
BEGIN
SET @firstpos=@pos
SET @strlen=8
END
SET @pos=PATINDEX('%[1][0-9]/[
IF @pos<@firstpos
BEGIN
SET @firstpos=@pos
SET @strlen=7
END
SET @pos=PATINDEX('%[1-9]/[0-9
IF @pos<@firstpos
BEGIN
SET @firstpos=@pos
SET @strlen=9
END
SET @pos=PATINDEX('%[1-9]/[1-9
IF @pos<@firstpos
BEGIN
SET @firstpos=@pos
SET @strlen=8
END
SET @pos=PATINDEX('%[1][0-9]/[
IF @pos<@firstpos
BEGIN
SET @firstpos=@pos
SET @strlen=10
END
SET @pos=PATINDEX('%[1][0-9]/[
IF @pos<@firstpos
BEGIN
SET @firstpos=@pos
SET @strlen=9
END
IF @firstpos>0
RETURN substring(@str,@firstpos,@
end
go
ASKER
create function dbo.fnNumbersFromStr(@str varchar(8000))
returns varchar(8000)
as
begin
while patindex('%[^0-9/]%',@str)
Set @str = rtrim(ltrim(replace(@str,s
return @str
end
go
What you’ve given me has gotten me closer, thanks again.