UDF inconsistent returns

Question

UDF inconsistent returns

Stuart Kinnear

Valued Member

Points: 67
More actions
October 20, 2006 at 5:17 am

#115145

I have created an UDF to extract dates from a general text field. When I test the UDF using an input string sampled from the database it operates fine. When I use the field (varchar) it fails randomly, yet a test on any one failure yields results. Been banging my head furiously without success !
eg. here I want 28/07/2002 returned (ultimately I want the SQL date so I can do a datediff)
SELECT [TestDB].dbo.GetDateFromString('Rental No. 00088 Items: GIZMO (CW105715) Rental Period: 4 weeks 1 day @ $21.43 per day Period: 01/07/2002 to 28/07/2002 PATIENT NAME: MRS CAMPBELL TO STRAKE TO
','% to %/[0-9]%/[0-9][0-9]%','/') AS DayTo
The function is as below:
ALTER FUNCTION [dbo].[GetDateFromString]
(
@String AS varchar(200)
, @Pattern AS varchar(100)
, @DateDelim AS varchar(5)
)
-- RETURNS smalldatetime --
RETURNS varchar(200)
AS
BEGIN
-- Declare the return variable here
-- DECLARE @ReturnDateAS smalldatetime
DECLARE @ReturnDateAS char(20)
DECLARE @TempAS char(200)
DECLARE @TempDateAS varchar(200)
DECLARE @PosPatternAS smallint
DECLARE @Pos AS smallint
DECLARE @DateStyle1AS varchar(50)
DECLARE @DateStyle2AS varchar(50)
DECLARE @DateStyle3AS varchar(50)
DECLARE @DateStyle4AS varchar(50)
DECLARE @DateStyle5AS varchar(50)
DECLARE @DateStyle6AS varchar(50)
DECLARE @DateStyle7AS varchar(50)
DECLARE @DateStyle8AS varchar(50)
DECLARE @DateStyleUsedAS varchar(50)
DECLARE @PartDayAS varchar(2)
DECLARE @PartMonthAS varchar(2)
DECLARE @PartYearAS varchar(4)
DECLARE @PatDateDelimAS varchar(10)
SET @DateStyle1 = '%[ 0-9][0-9][' + @DateDelim + '][ 0-9][0-9][' + @DateDelim + '][0-9][0-9][0-9][0-9] %'
SET @DateStyle2 = '%[ 0-9][0-9][' + @DateDelim + '][0-9][' + @DateDelim + '][0-9][0-9][0-9][0-9] %'
SET @DateStyle3 = '%[0-9][' + @DateDelim + '][ 0-9][0-9][' + @DateDelim + '][0-9][0-9][0-9][0-9] %'
SET @DateStyle4 = '%[0-9][' + @DateDelim + '][0-9][' + @DateDelim + '][0-9][0-9][0-9][0-9] %'
SET @DateStyle5 = '%[ 0-9][0-9][' + @DateDelim + '][ 0-9][0-9][' + @DateDelim + '][0-9][0-9] %'
SET @DateStyle6 = '%[ 0-9][0-9][' + @DateDelim + '][0-9][' + @DateDelim + '][0-9][0-9] %'
SET @DateStyle7 = '%[0-9][' + @DateDelim + '][0-9][' + @DateDelim + '][0-9][0-9] %'
SET @DateStyle8 = '%[0-9][' + @DateDelim + '] [0-9][' + @DateDelim + '][0-9][0-9] %'
SET @PosPattern = ISNULL(PATINDEX(@Pattern,@String),0)
IF @PosPattern >0
BEGIN
SET @Temp = SUBSTRING(@String,@PosPattern,LEN(@String) - @PosPattern + 1)
SET @Pos = PATINDEX(@DateStyle1,@Temp)
SET @DateStyleUsed = 'DateStyle1'
IF @Pos = 0
BEGIN
SET @Pos = PATINDEX(@DateStyle2,@Temp)
SET @DateStyleUsed = 'DateStyle2'
END
IF @Pos = 0
BEGIN
SET @Pos = PATINDEX(@DateStyle3,@Temp)
SET @DateStyleUsed = 'DateStyle3'
END
IF @Pos = 0
BEGIN
SET @Pos = PATINDEX(@DateStyle4,@Temp)
SET @DateStyleUsed = 'DateStyle4'
END
IF @Pos = 0
BEGIN
SET @Pos = PATINDEX(@DateStyle5,@Temp)
SET @DateStyleUsed = 'DateStyle5'
END
IF @Pos = 0
BEGIN
SET @Pos = PATINDEX(@DateStyle6,@Temp)
SET @DateStyleUsed = 'DateStyle6'
END
IF @Pos = 0
BEGIN
SET @Pos = PATINDEX(@DateStyle7,@Temp)
SET @DateStyleUsed = 'DateStyle7'
END
IF @Pos = 0
BEGIN
SET @Pos = PATINDEX(@DateStyle8,@Temp)
SET @DateStyleUsed = 'DateStyle8'
END
IF @Pos = 0
BEGIN
SET @ReturnDate = 'FAIL @PosPattern=' + CAST(@PosPattern AS varchar(3)) + ' @Temp=' + @Temp
RETURN @ReturnDate
END
ELSE
BEGIN
SET @TempDate = SUBSTRING(@Temp,@Pos,LEN(@Temp)-@Pos + 1)
--RETURN @DateStyleUsed + ', @Pos= ' + CAST(@Pos AS varchar(4)) + ' @TempDate=' + @TempDate
--RETURN CAST(@Pos AS varchar(4)) + ' ' + @Temp
END
END
ELSE
BEGIN
SET @ReturnDate = NULL
RETURN @ReturnDate
END
-- Extract the components of the date and create a SQL date
SET @PatDateDelim = '%[' + @DateDelim + ']%'
SET @Pos = PATINDEX(@PatDateDelim,@TempDate)
--RETURN CAST(@Pos AS varchar(4)) + ' Search ' + @PatDateDelim + ' Within ' + @TempDate
SET @PartDay = LEFT(@TempDate,@Pos - 1)
--RETURN @PartDay
SET @TempDate = SUBSTRING(@TempDate,@Pos + 1,LEN(@TempDate) - @Pos )
SET @Pos = PATINDEX(@PatDateDelim,@TempDate)
SET @PartMonth = LEFT(@TempDate,@Pos - 1)
SET @TempDate = SUBSTRING(@TempDate,@Pos + 1,LEN(@TempDate) - @Pos )
SET @Pos = PATINDEX('%[0-9] %',@TempDate)
-- RETURN CAST(@Pos AS varchar(4)) + ' Year Within ' + @TempDate
SET @PartYear = LEFT(@TempDate,@Pos)
SET @PartYear=RTRIM(LTRIM(@PartYear))
IF LEN(@PartYear)=2
BEGIN
SET @PartYear = '20' + @PartYear
END
SET @PartMonth=RTRIM(LTRIM(@PartMonth))
IF LEN(@PartMonth)<2
BEGIN
SET @PartMonth = '0' + @PartMonth
END
SET @PartDay=RTRIM(LTRIM(@PartDay))
IF LEN(@PartDay)<2
BEGIN
SET @PartDay = '0' + @PartDay
END
SET @TempDate = @PartYear + '-' + @PartMonth + '-' + @PartDay
--SET @ReturnDate = CAST(@TempDate AS smalldatetime)
SET @ReturnDate = @TempDate
RETURN @ReturnDate
END

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

Hmm.. the function seems to me too complicated, it should be possible to do it in some other way.

Is the text "Period: 01/07/2002 to 28/07/2002" always the same (code-generated) or can it contain additional spaces, different order of day/month in certain rows, various delimiters and so on?

Are there any other things to be aware of, like for example that a date may occur elsewhere in the string but has to be ignored there?

As to the format of your result, I would recommend to use always the ASCII standard for dates, which is YYYYMMDD, without any delimiters. This can then be easily converted into datetime data type and is not influenced by various SQLS and environment settings.

Stuart Kinnear Valued Member Points: 67 More actions · Answer 2

Unfortunately the input text stream is not as consistent as it should be hence the different ways of pattern matching the dates. eg Period: 01/07/2002 to 28/07/2002,

Period: 01/07/02 to 28/07/02, Period: 1/07/2002 to 28/07/2002, Period: 1/7/2002 to 28/7/2002, Period: 01/ 7/2002 to 28/ 7/2002. for some unknown reason the original programmer stored all this invoice data in a text field & changed the program over time & yours truly now has the task of splitting it all out.

As I say, the function as it stands works well if I pass in the variable, but when I use a field I get spurious returns. I wanted to do it in SQL as I thought it would be quick and efficient....

Vladan SSC-Insane Points: 22072 More actions · Answer 3

OMG... I hope that this is a one-time conversion to correct format, not something you'll have to do permanently (on every new row)?

I am not sure that SQL Server is the best tool to do this. It is designed to work with sets of data, while here you need to treat each row differently, probably skip some rows altogether and so on... it is more of a process than simple transformation. It could be easier to do it outside SQL Server - although I'm sure it can be done here.

Can you give me an example how you call the function using column name when it fails? What I mean is, is the dataset restricted in some way and did you check that applied conditions don't allow rows with incorrect (non-convertible) value in the column?

Ken McKelvey SSCoach Points: 18838 More actions · Answer 4

If the date you are looking for always terminates with 'PATIENT NAME:', why bother with a function? eg:

-- Test Data

DECLARE @t TABLE

(

TID INT not null

,TText TEXT null

)

INSERT @t

SELECT 1, 'Rental No. 00088 Items: GIZMO (CW105715) Rental Period: 4 weeks 1 day @ $21.43 per day Period: 01/07/2002 to 28/07/2002 PATIENT NAME: MRS CAMPBELL TO STRAKE TO'

UNION ALL

SELECT 2, 'Rental No. 00088 Items: GIZMO (CW105715) Rental Period: 4 weeks 1 day @ $21.43 per day Period: 01/07/2002 to 28/7/2002 PATIENT NAME: MRS CAMPBELL TO STRAKE TO'

UNION ALL

SELECT 3, 'Rental No. 00088 Items: GIZMO (CW105715) Rental Period: 4 weeks 1 day @ $21.43 per day Period: 01/07/2002 to 28/ 7/2002 PATIENT NAME: MRS CAMPBELL TO STRAKE TO'

-- Get the date.

SELECT CONVERT(SMALLDATETIME, SUBSTRING(TText, PATINDEX('%PATIENT NAME:%', TText) - 11, 10), 103) AS FinalDate

FROM @t

WHERE PATINDEX('%PATIENT NAME:%', TText) > 12

David Burrows SSC Guru Points: 65141 More actions · Answer 5

I agree with Vladan that it is over complicated

I believe this would achieve the same

SET DATEFORMAT DMY

SET @String = REPLACE(@String,' ','')

SET @String = SUBSTRING(@String,PATINDEX('%to[0-9][0-9/][0-9/]%',@String)+2,255)

SET @String = LEFT(@String,PATINDEX('%[^0-9/]%',@String)-1)

SELECT CAST(@String as datetime)

You may need to add validation to prevent errors

Far away is close at hand in the images of elsewhere.
Anon.

Ken McKelvey SSCoach Points: 18838 More actions · Answer 6

I missed one of your dates in my previous post. Now corrected:

-- Test Data

DECLARE @t TABLE

(

TID INT not null

,TText TEXT null

)

INSERT @t

SELECT 1, 'Rental No. 00088 Items: GIZMO (CW105715) Rental Period: 4 weeks 1 day @ $21.43 per day Period: 01/07/2002 to 28/07/2002 PATIENT NAME: MRS CAMPBELL TO STRAKE TO'

UNION ALL

SELECT 2, 'Rental No. 00088 Items: GIZMO (CW105715) Rental Period: 4 weeks 1 day @ $21.43 per day Period: 01/07/2002 to 28/7/2002 PATIENT NAME: MRS CAMPBELL TO STRAKE TO'

UNION ALL

SELECT 3, 'Rental No. 00088 Items: GIZMO (CW105715) Rental Period: 4 weeks 1 day @ $21.43 per day Period: 01/07/2002 to 28/ 7/2002 PATIENT NAME: MRS CAMPBELL TO STRAKE TO'

UNION ALL

SELECT 4, 'Rental No. 00088 Items: GIZMO (CW105715) Rental Period: 4 weeks 1 day @ $21.43 per day Period: 01/07/2002 to 8/7/2002 PATIENT NAME: MRS CAMPBELL TO STRAKE TO'

-- Get the date.

SELECT CONVERT(SMALLDATETIME

,CASE WHEN SUBSTRING(TText, PATINDEX('%PATIENT NAME:%', TText) - 11, 1) LIKE 'o'

THEN SUBSTRING(TText, PATINDEX('%PATIENT NAME:%', TText) - 10, 9)

ELSE SUBSTRING(TText, PATINDEX('%PATIENT NAME:%', TText) - 11, 10) END

, 103) AS FinalDate

FROM @t

WHERE PATINDEX('%PATIENT NAME:%', TText) > 12

Vladan SSC-Insane Points: 22072 More actions · Answer 7

Nice job there, Ken.. I'm just afraid that Stuart will now say that "PATIENT NAME" is not always included. It will be "Pat.Name" or even "Nurse Name" in some rows, and what then...? That's why I was asking whether the data are consistent in some way - not wanting to try for a solution while it is unclear with what I have to work. Yeah, I'm lazy, I know...

Stuart Kinnear Valued Member Points: 67 More actions · Answer 8

Here is another input string just to show what I'm up against. I probably will do this quicker in VB, but for the exercise I thought I'd persist a bit longer with SQL.

Rental No. 00018 SERIAL NO. CW104753 Rental Period: 4 weeks 1 day @ $21.43 per day PATIENT LIM TO CLIFFE 07/06/02 MATTRAESS TRANSFERRED TO NORTH 1 03/07/02 Period: 05/06/2002 to 03/07/2002 LIM TO CLIFFE 7/6

Here I want the dates 05/06/2002 and 03/07/2002 (from Period: 05/06/2002 to 03/07/2002), note that Period: is also seen in 'Rental Period:' and ' to ' is embedded in 'LIM TO CL' with another date soon following ( NORTH 1 03/07/02)

Stuart Kinnear Valued Member Points: 67 More actions · Answer 9

You are right - the only thing that is consistent is the block "Period: 05/06/2002 to 03/07/2002" with the aforementioned variants on the dates. (dd/mm/yyyy, d/mm/yyyy,d/m/yyyy,dd/mm/yy etc)

David Burrows SSC Guru Points: 65141 More actions · Answer 10

OK then

SELECT

CAST(LEFT(STUFF([String],1,PATINDEX('%Period: [0-9/ ][0-9/ ][0-9/ ]%',[String])+7,''),PATINDEX('%[^0-9/ ]%',STUFF([String],1,PATINDEX('%Period: [0-9/ ][0-9/ ][0-9/ ]%',[String])+7,''))-1) as datetime) AS [FirstDate],

CAST(LEFT(SUBSTRING([String],PATINDEX('%Period: [0-9/ ][0-9/ ][0-9/ ]%',[String])+7+PATINDEX('%[^0-9/ ]%',STUFF([String],1,PATINDEX('%Period: [0-9/ ][0-9/ ][0-9/ ]%',[String])+7,''))+2,255),PATINDEX('%[^0-9/ ]%',SUBSTRING([String],PATINDEX('%Period: [0-9/ ][0-9/ ][0-9/ ]%',[String])+7+PATINDEX('%[^0-9/ ]%',STUFF([String],1,PATINDEX('%Period: [0-9/ ][0-9/ ][0-9/ ]%',[String])+7,''))+2,255))-1) as datetime) AS [SecondDate]

FROM [sometable]

Far away is close at hand in the images of elsewhere.
Anon.

Stuart Kinnear Valued Member Points: 67 More actions · Answer 11

Unfortunately this failed with The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

One of the reasons I wanted to use an UDF is to be able to deal with errors gracefully.

Ken McKelvey SSCoach Points: 18838 More actions · Answer 12

Following David’s suggestion, which will require your TEXT column to always be <= 8000 characters and to be converted to VARCHAR, try this:

-- Test Data

DECLARE @t TABLE

(

TID INT not null

,TText TEXT null

)

INSERT @t

SELECT 1, 'Rental No. 00088 Items: GIZMO (CW105715) Rental Period: 4 weeks 1 day @ $21.43 per day Period: 01/07/2002 to 28/07/2002 PATIENT NAME: MRS CAMPBELL TO STRAKE TO'

UNION ALL

SELECT 2, 'Rental No. 00088 Items: GIZMO (CW105715) Rental Period: 4 weeks 1 day @ $21.43 per day Period: 1/07/2002 to 28/7/2002 PATIENT NAME: MRS CAMPBELL TO STRAKE TO'

UNION ALL

SELECT 3, 'Rental No. 00088 Items: GIZMO (CW105715) Rental Period: 4 weeks 1 day @ $21.43 per day Period: 1/07/2002 to 28/ 7/2002 PATIENT NAME: MRS CAMPBELL TO STRAKE TO'

UNION ALL

SELECT 4, 'Rental No. 00088 Items: GIZMO (CW105715) Rental Period: 4 weeks 1 day @ $21.43 per day Period: 01/07/2002 to 8/7/2002 PATIENT NAME: MRS CAMPBELL TO STRAKE TO'

UNION ALL

SELECT 5, 'Rental No. 00018 SERIAL NO. CW104753 Rental Period: 4 weeks 1 day @ $21.43 per day PATIENT LIM TO CLIFFE 07/06/02 MATTRAESS TRANSFERRED TO NORTH 1 03/07/02 Period: 05/06/2002 to 03/07/2002 LIM TO CLIFFE 7/6'

SELECT D.TID

,CONVERT(SMALLDATETIME, LEFT(D.DateRange, CHARINDEX('to', D.DateRange) - 1), 103) AS PeriodStart

,CONVERT(SMALLDATETIME, SUBSTRING(D.DateRange

,CHARINDEX('to', D.DateRange) + 2

,PATINDEX('%[1-2][0-9][0-9][0-9]%', SUBSTRING(D.DateRange, CHARINDEX('to', D.DateRange), 12)) + 1)

, 103) AS PeriodEnd

FROM (

SELECT T.TID

,SUBSTRING(REPLACE(CAST(T.TText AS VARCHAR(8000)), ' ', ''), PATINDEX('%Period:[0-9][0-9/][0-9/]%'

, REPLACE(CAST(T.TText AS VARCHAR(8000)), ' ', '')) + 7, 50)

FROM @t T

WHERE PATINDEX('%Period:[0-9][0-9/][0-9/]%', REPLACE(CAST(T.TText AS VARCHAR(8000)), ' ', '')) > 0 )

D (TID, DateRange)

David Burrows SSC Guru Points: 65141 More actions · Answer 13

In that case go back to my original solution and expand it

DECLARE @index int

SET @String = REPLACE(@String,' ','')

SET @index = PATINDEX('%Period:[0-9][0-9/][0-9/]%',@String)

SET @String = STUFF(@String,1,@index + 6,'')

SET @index = PATINDEX('%[^0-9/]%',@String)

SELECT CAST(LEFT(@String,@index-1) as datetime)

SET @String = STUFF(@String,1,@index+1,'')

SET @index = PATINDEX('%[^0-9/]%',@String)

SELECT CAST(LEFT(@String,@index-1) as datetime)

Now you can check @index value to check for missing data

You may need to supply another parameter to decide which date to return

Far away is close at hand in the images of elsewhere.
Anon.

Stuart Kinnear Valued Member Points: 67 More actions · Answer 14

Thanks to all that assisted. I had to work extra hard on this one, a simple solution just would not cut it as there was a couple of US date formats (when can the world be metric and have one sensible date format ?)

Here is one half of my solution;

ALTER FUNCTION [dbo].[InvoiceLinesDateFrom]

(

@Description AS varchar(400)

)

RETURNS datetime

-- RETURNS varchar(60)

AS

BEGIN

-- Declare the return variable here

DECLARE @ReturnDateAS datetime

--DECLARE @ReturnDateAS varchar(60)

DECLARE @ReferenceDateAS varchar(60)

DECLARE @TempDateAS char(100)

DECLARE @Pos AS smallint

DECLARE @PartDayAS varchar(2)

DECLARE @PartMonthAS varchar(2)

DECLARE @PartYearAS varchar(4)

DECLARE @PatDateDelimAS varchar(10)

DECLARE @DateDelimAS varchar(1)

DECLARE @DateTypeAS varchar(15)

BEGIN

SET @DateType = '~/4' -- For Debugging Purposes

--- Example

--- Rental No. 15247 Items: VIACLIN (OV2161) Rental Period: 11 weeks 6 days @ $9.285714 per day Period: 11/05/2005 to 01/08/2005 Patient ID: TOMASON (B.3)

--- Rental No. 11139 Items: BIWAVE (BC104094) Rental Period: 2822 weeks 4 days @ $14.5 per day Period: 05/12/1950 to 07/01/2005 Patient ID: BOTTONI (B.2)

--- Rental No. 00293 Items: CAIRWAVE (CW106081) Rental Period: 9 weeks 2 days @ $21.43 per day Period: 10/29/02 to 1/1/03 Patient I.D.: Craig

SET @Pos = PATINDEX('%Period: [0-9 ][0-9/][0-9/][0-9/]%',@Description)

SET @TempDate = (SUBSTRING(@Description, @Pos + 8, LEN(@Description) - (@Pos + 8)))

SET @Pos = PATINDEX('%Period: [0-9 ][0-9/][0-9/][0-9/]%',@TempDate)

IF @Pos>0

BEGIN

SET @TempDate = (SUBSTRING(@TempDate, @Pos + 8, LEN(@TempDate) - (@Pos + 8)))

END

SET @Pos = PATINDEX('% to %',@TempDate)

SET @TempDate = SUBSTRING(@TempDate,1,@Pos - 1)

END

SET @ReferenceDate = @TempDate

-- Extract the components of the date and create a SQL date

SET @DateDelim = '/'

SET @PatDateDelim = '%[' + @DateDelim + ']%'

SET @Pos = PATINDEX(@PatDateDelim,@TempDate)

--RETURN CAST(@Pos AS varchar(4)) + ' Search ' + @PatDateDelim + ' Within ' + @TempDate

SET @PartDay = LEFT(@TempDate,@Pos - 1)

--RETURN @PartDay

SET @TempDate = SUBSTRING(@TempDate,@Pos + 1,LEN(@TempDate) - @Pos )

SET @Pos = PATINDEX(@PatDateDelim,@TempDate)

SET @PartMonth = LEFT(@TempDate,@Pos - 1)

IF CAST(@PartMonth AS smallint) > 12

-- Using US date format

BEGIN

SET @TempDate = @ReferenceDate

SET @Pos = PATINDEX(@PatDateDelim,@TempDate)

SET @PartMonth = LEFT(@TempDate,@Pos - 1)

SET @TempDate = SUBSTRING(@TempDate,@Pos + 1,LEN(@TempDate) - @Pos )

SET @PartDay = LEFT(@TempDate,@Pos - 1)

END

SET @TempDate = SUBSTRING(@TempDate,@Pos + 1,LEN(@TempDate) - @Pos )

SET @Pos = PATINDEX('%[0-9] %',@TempDate)

-- RETURN CAST(@Pos AS varchar(4)) + ' Year Within ' + @TempDate

SET @PartYear = LEFT(@TempDate,@Pos)

SET @PartYear=RTRIM(LTRIM(@PartYear))

IF LEN(@PartYear)=2

BEGIN

SET @PartYear = '20' + @PartYear

END

SET @PartMonth=RTRIM(LTRIM(@PartMonth))

IF LEN(@PartMonth)<2

BEGIN

SET @PartMonth = '0' + @PartMonth

END

SET @PartDay=RTRIM(LTRIM(@PartDay))

IF LEN(@PartDay)<2

BEGIN

SET @PartDay = '0' + @PartDay

END

IF CAST(@PartDay AS smallint)31

BEGIN

-- RETURN '~ @PartDay '+ @PartDay + @ReferenceDate

SET @PartDay = 31

END

IF CAST(@PartDay AS smallint)>30

BEGIN

IF CAST(@PartMonth AS smallint) = 4