I guess I misunderstood your initial question. I thought you were researching a particular document trying to track something down instead of trying to automate a report. I have used the text from docdata with the "Imported by:" many times to track down which users are importing documents improperly (without observations). So much that I ended up writing a function called "stringbetween" which literally finds the string between two other strings. The function has 3 values, input string (docdata.data) start string ("Imported By:") and end string (" on "). I ended up writing the Function for both Oracle (what we use for emr) and MSSQL. The oracle one is more robust with default values for the start and end strings so that you can leave them out and start/stop at the beginning/end.
To find the user who imported it the SQL looks something like this:
SELECT STRINGBETWEEN(DOCDATA.DATA,'Imported By: ', ' on ') IMPORTED_BY
FROM DOCDATA WHERE ...
Here is the code for MSSQL:
ALTER FUNCTION [dbo].[StringBetween] (@TEXT VARCHAR(MAX), @START VARCHAR(MAX), @END VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @OUTPUT AS VARCHAR(MAX);
SELECT @OUTPUT = substring(@TEXT,
charindex(@START, @TEXT) + LEN(@START),
(charindex(@END, @TEXT, charindex(@START, @TEXT) + LEN(@START)) - (charindex(@START, @TEXT) + LEN(@START))));
RETURN @OUTPUT;
END;
Oracle:
create or replace FUNCTION STRINGBETWEEN (TEXT IN VARCHAR2, START_TEXT IN VARCHAR2 DEFAULT NULL, END_TEXT IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
IS OUTPUT VARCHAR2(4000);
BEGIN
CASE
WHEN START_TEXT IS NULL AND END_TEXT IS NULL THEN
SELECT TEXT INTO OUTPUT FROM DUAL;
WHEN START_TEXT IS NULL THEN
SELECT SUBSTR(
TEXT,
1,
(INSTR(TEXT, END_TEXT) -1)
)
INTO OUTPUT FROM DUAL;
WHEN END_TEXT IS NULL THEN
SELECT SUBSTR(
TEXT,
INSTR(TEXT, START_TEXT) + LENGTH(START_TEXT) --find the location of the start text and start after it
)
INTO OUTPUT FROM DUAL;
ELSE
SELECT SUBSTR(
TEXT,
INSTR(TEXT, START_TEXT) + LENGTH(START_TEXT), --find the location of the start text and start after it
(INSTR(TEXT, END_TEXT, INSTR(TEXT, START_TEXT) + LENGTH(START_TEXT)) - (INSTR(TEXT, START_TEXT) + LENGTH(START_TEXT))) --find the first occurance of the end text after the starttext
)
INTO OUTPUT FROM DUAL;
END CASE;
RETURN(OUTPUT);
END;
Posted : August 28, 2017 1:44 pm
