UDF inconsistent returns
-
The only thing is certain here:
this must NOT be done in SQL Server.
There is nothing about datasets, nothing about data at all. It's all about reading and interpreting strings.
I'm pretty sure for the time you have already spent on this function and will spend yet the module saved data pieces into database separately and correctly would be finished.
And you will execute such functionality in multi-user environment your server will be down all the time.
_____________
Code for TallyGenerator -
You're right about this not being done in SQL server.
- difficulty in debugging
- difficulty in error control
I did this as an education, but probably shouldn't have due to the pressures of time. The migration is a once off (when it finally happens), so multi user is not an issue.
I found that:
the SQL server 2005 is flaky compared to SQL server 2000. I'd be stepping through and all of a sudden I'd end up in limbo land.
That it might have been better in this instance to reverse the string and search backwards as the key parts are at the end of the string and notes were obscuring the matching.
That it is better to disassemble the pattern matches into small components so that debugging is easier.
-
First of all, the format of the date just about doesn't matter here (EXCEPT FOR THE FACT THAT YOU HAVE MIXED US AND EUROPEAN DATES!!! ARG!!)... it can be any of the following formats and SQL Server will handle it... even with single digit years. These are all in the various permutations of the "DMY" format...
SET DATEFORMAT DMY --<< THIS WAS DAVID BURROWS' IDEA AND WORKS GREAT!!!
SELECT CAST('1/5/2006' AS DATETIME)
SELECT CAST('1/05/2006' AS DATETIME)
SELECT CAST('01/5/2006' AS DATETIME)
SELECT CAST('01/05/2006' AS DATETIME)
SELECT CAST('1/5/06' AS DATETIME)
SELECT CAST('1/05/06' AS DATETIME)
SELECT CAST('01/5/06' AS DATETIME)
SELECT CAST('01/05/06' AS DATETIME)
SELECT CAST('1/5/6' AS DATETIME)
SELECT CAST('1/05/6' AS DATETIME)
SELECT CAST('01/5/6' AS DATETIME)
SELECT CAST('01/05/6' AS DATETIME)
Knowing that, "all" we have to do is find the correct pattern
Because the format of the dates can vary so much and the word "Period:" can appear more than once, we need to step through the characters one-by-one and do some "analysis". But, we won't be using overt "RBAR" to do that, folks...If you don't already have a "Tally" table, now's the time to build one...
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates for date calcs when needed
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
Ok... that wasn't so bad... now, let's build a function to correctly parse the input string based on the correct pattern of the word "Period:" followed by something that looks like a date followed by the word " TO " followed by another thing that looks like a date...
CREATE FUNCTION dbo.fnFindPeriodDate
(
@String VARCHAR(8000), --Holds the string to parse
@DateType VARCHAR(8) --Holds which date to find as 'FromDate' or 'ToDate'
)
RETURNS DATETIME
AS
BEGIN
--===== Declare local variables
DECLARE @Result1 VARCHAR(8000) --First pass result contains digits, /, P's, and spaces
DECLARE @Result2 VARCHAR(8000) --Second pass contains the two dates separated by a single space
DECLARE @FromDate DATETIME --Holds the left most date
DECLARE @ToDate DATETIME --Holds the right most date
--===== First pass strips out most of the garbage and leaves digits, dates, and key characters P and space
SELECT @Result1 = COALESCE(@Result1,'')+SUBSTRING(@String,N,1)
FROM dbo.Tally WITH (NOLOCK)
WHERE ( SUBSTRING(@String,N,1) LIKE '[0-9/]'
OR SUBSTRING(@String,N,4) = ' TO '
OR SUBSTRING(@String,N,7) = 'Period:'
)
AND N<=LEN(@String)
--===== Second pass returns the two dates separated by a space but only if they follow the key character P
-- within 21 characters of the P
SELECT @Result2 = CASE
WHEN SUBSTRING(@Result1,N,1) = 'P'
AND SUBSTRING(@Result1,N,22) LIKE 'P%[0-9]%/%[0-9]%/%[0-9][ ]%[0-9]%/%[0-9]%/%[0-9]'
THEN NULL
ELSE COALESCE(@Result2,'')+SUBSTRING(@Result1,N,1)
END
FROM dbo.Tally WITH (NOLOCK)
WHERE SUBSTRING(@Result1,N,1) LIKE '[0-9/TP ]'
AND N<=LEN(@Result1)
--===== Get rid of trailing spaces and replace embedded spaces with period so can use PARSENAME
SET @Result2 = REPLACE(RTRIM(@Result2),' ','.')
--===== All set, split the dates
SET @FromDate = CAST(PARSENAME(@Result2,2) AS DATETIME)
SET @ToDate = CAST(PARSENAME(@Result2,1) AS DATETIME)
--===== Return which ever date was called for by @DateType.
RETURN CASE
WHEN @DateType = 'FromDate'
THEN @FromDate
WHEN @DateType = 'ToDate'
THEN @ToDate
ELSE NULL
END
END
...AND now, using Ken's good test data, let's demo the function... do notice that I've also added more backslashes in some key areas just to demo that this function will only find the correct pattern...
SET DATEFORMAT DMY --<<< DAVID BURROWS HAD IT RIGHT!! YOU MUST INCLUDE 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: 1/06/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/5/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/03/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/02/02 to 8/7/2002 PATIENT NAME: MRS CAMPBELL TO STRAKE TO'
--===== Demo the function
SELECT dbo.fnFindPeriodDate(TText,'FromDate') AS FromDate,
dbo.fnFindPeriodDate(TText,'ToDate') AS ToDate
FROM @t
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply