CHARINDEX DYNAMICALLY to get string
I am having trouble with getting exactly what I want using CHARINDEX in my sql query. I am trying to do it dynamically, however, due to the the nature of the data, I have not found a way. I want to do it without a CASE statement, I just want to select whatever text is between PRIORITY : and SYMPTOMS : . There is also a possibility that PRIORITY : may come after SYMPTOMS :
Below is my code, along with a sample of the data I am working with. Thank you for any help.
Sample data:
NOTE1
PRIORITY : ROUTINE
SYMPTOMS : HEADACHE
TREATMENT PLAN :
NOTE2
SYMPTOMS : NOTES ATTACHED
TREATMENT PLAN :
PRIORITY : URGENT
NOTE3
ONGOING COUGH. ONLY OCCASIONAL WHEEZING. NO EVIDENCE OF AIRFLOW OBSTRUCTION. MEASURED
IS 99% OF IS FAVORABLE AT 79 ROOM AIR OXYGENATION IS
AILABLE DIAGNOSTICS INCLUDE FACIAL MRI DEMONSTRATING MILD SINUS INFLA
TREATMENT PLAN : FOLLOW UP
PRIORITY : ROUTINE
SYMPTOMS : RETURNS TO OFFICE.PRINCIPAL TEXT OBSTRUCTIVE TEXT TEXT WITH DEPENDENCE ON CONTINUOUS POSITIVE AIRWAY PRESSUR
E VENTILATION AT BEDTIME 2. ASSOCIATED HISTORY OF TEXT 3.
Below is my code, along with a sample of the data I am working with. Thank you for any help.
SELECT
AUTHNO, CHARINDEX('PRIORITY',NOTES) AS CHRIND, CHARINDEX('SYMPTOMS',NOTES) AS CHRIND2,
CASE WHEN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(NOTES,CHARINDEX('PRIORITY:', NOTES),CHARINDEX('SYMPTOMS',NOTES)-CHARINDEX('PRIORITY:', NOTES)),'PRIORITY :',''),CHAR(7),''),CHAR(9),''),CHAR(10),''),CHAR(12),''),CHAR(13),''))) LIKE '%ROUTINE%' THEN 'ROUTINE'
WHEN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(NOTES,CHARINDEX('PRIORITY:', NOTES),CHARINDEX('SYMPTOMS',NOTES)-CHARINDEX('PRIORITY:', NOTES)),'PRIORITY :',''),CHAR(7),''),CHAR(9),''),CHAR(10),''),CHAR(12),''),CHAR(13),''))) LIKE '%RETRO%' THEN 'RETRO'
WHEN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(NOTES,CHARINDEX('PRIORITY:', NOTES),CHARINDEX('SYMPTOMS',NOTES)-CHARINDEX('PRIORITY:', NOTES)),'PRIORITY :',''),CHAR(7),''),CHAR(9),''),CHAR(10),''),CHAR(12),''),CHAR(13),''))) LIKE '%URGENT%' THEN 'URGENT'
ELSE 'ROUTINE' END
AS PRIORITY
FROM dbo.MyNotesView WHERE SEQUENCE = '1' AND NOTES LIKE '%PRIORITY%' Sample data:
NOTE1
PRIORITY : ROUTINE
SYMPTOMS : HEADACHE
TREATMENT PLAN :
NOTE2
SYMPTOMS : NOTES ATTACHED
TREATMENT PLAN :
PRIORITY : URGENT
NOTE3
ONGOING COUGH. ONLY OCCASIONAL WHEEZING. NO EVIDENCE OF AIRFLOW OBSTRUCTION. MEASURED
IS 99% OF IS FAVORABLE AT 79 ROOM AIR OXYGENATION IS
AILABLE DIAGNOSTICS INCLUDE FACIAL MRI DEMONSTRATING MILD SINUS INFLA
TREATMENT PLAN : FOLLOW UP
PRIORITY : ROUTINE
SYMPTOMS : RETURNS TO OFFICE.PRINCIPAL TEXT OBSTRUCTIVE TEXT TEXT WITH DEPENDENCE ON CONTINUOUS POSITIVE AIRWAY PRESSUR
E VENTILATION AT BEDTIME 2. ASSOCIATED HISTORY OF TEXT 3.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No usable test data (CREATE TABLE and INSERT statements, so we can create the data ourselves), so this is possibly just a rough cut at the code.
SELECT
AUTHNO,
SUBSTRING(NOTES, start_of_priority_value, CHARINDEX(CHAR(10), NOTES + CHAR(10),
start_of_priority_value) - start_of_priority_value) AS PRIORITY
FROM dbo.MyNotesView
CROSS APPLY (
SELECT CHARINDEX('PRIORITY:', NOTES) + 9 AS start_of_priority_value
) AS alias1
WHERE SEQUENCE = '1' AND NOTES LIKE '%PRIORITY%'
SELECT
AUTHNO,
SUBSTRING(NOTES, start_of_priority_value, CHARINDEX(CHAR(10), NOTES + CHAR(10),
start_of_priority_value) - start_of_priority_value) AS PRIORITY
FROM dbo.MyNotesView
CROSS APPLY (
SELECT CHARINDEX('PRIORITY:', NOTES) + 9 AS start_of_priority_value
) AS alias1
WHERE SEQUENCE = '1' AND NOTES LIKE '%PRIORITY%'
(no points...)
Knew @Scott PletcherSenior would be by with his handy CROSS APPLY ;-) I tested it and it's much more elegant than what I came up with.
Only thing I changed was to add a space in between PRIORITY and ":", and change the length to 10. Worked like a champ after that.
CROSS APPLY (
SELECT CHARINDEX('PRIORITY(space):', NOTES) + 10 AS start_of_priority_value
) AS alias1
Knew @Scott PletcherSenior would be by with his handy CROSS APPLY ;-) I tested it and it's much more elegant than what I came up with.
Only thing I changed was to add a space in between PRIORITY and ":", and change the length to 10. Worked like a champ after that.
CROSS APPLY (
SELECT CHARINDEX('PRIORITY(space):', NOTES) + 10 AS start_of_priority_value
) AS alias1
ASKER
Hi, this is helpful, thank you. HOWEVER, it does not always work to yield the expected result. When I used the query I included in my initial question, it yielded essentially the same result, that is, it works only if the first CHAR(10) is after PRIORITY, I should have included a more complex set of text that may be in the Notes field, like below. For the notes below, it does not yield the value of PRIORITY. Thank you.
TREATMENT PLAN : REQUESTING TEXT, WE WILL BE GOING OVER DI
AGNOSTIC TESTING- OKAY PER MANAGER.
PRIORITY : ROUTINE
SYMPTOMS : PLEASE SEE ATTACHED- TEXT TEST
TREATMENT PLAN : REQUESTING TEXT, WE WILL BE GOING OVER DI
AGNOSTIC TESTING- OKAY PER MANAGER.
PRIORITY : ROUTINE
SYMPTOMS : PLEASE SEE ATTACHED- TEXT TEST
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Considering the dataset has already been filtered where NOTES LIKE '%PRIORITY%'
And there can be multi values for priority such as 'urgent', 'routine' and others no doubt. Then why not do it as part of the select ? Make it more robust and check for 'other' conditions.
couple of questions...
will Priority always be of the assumed format 'PRIORITY : ' ie 10 characters ?
why not use WHERE SEQUENCE = 1 AND NOTES LIKE '%PRIORITY :%'
will Priority value/string always be terminated by a linefeed (ie CHAR(10)) ?
You obviously dont want bell, tab, linefeed,carriage return, etc as part of your results - do they happen ?
will the priority value always be uppercase ie 'URGENT' or 'ROUTINE' or 'RETRO' - in which case, is there a need / reason for "else 'routine'" in the case statement - would prefer a warning....
And there can be multi values for priority such as 'urgent', 'routine' and others no doubt. Then why not do it as part of the select ? Make it more robust and check for 'other' conditions.
couple of questions...
will Priority always be of the assumed format 'PRIORITY : ' ie 10 characters ?
why not use WHERE SEQUENCE = 1 AND NOTES LIKE '%PRIORITY :%'
will Priority value/string always be terminated by a linefeed (ie CHAR(10)) ?
You obviously dont want bell, tab, linefeed,carriage return, etc as part of your results - do they happen ?
will the priority value always be uppercase ie 'URGENT' or 'ROUTINE' or 'RETRO' - in which case, is there a need / reason for "else 'routine'" in the case statement - would prefer a warning....
select authno
,CASE when Priority_String_Extract like 'urgent%' then 'URGENT'
when Priority_String_Extract like 'retro%' then 'RETRO'
when Priority_String_Extract like 'routine%' then 'ROUTINE'
ELSE 'Unknown - check Notes' end as Priority -- or you could do ELSE 'ROUTINE' end as Priority
from MyNotesView
cross apply (select ltrim(substring(notes,charindex('PRIORITY :',Notes)+10,7))) Priority(Priority_String_Extract)
where sequence = '1' AND NOTES LIKE '%PRIORITY%'ASKER
will Priority always be of the assumed format 'PRIORITY : ' ie 10 characters ? Yes.
why not use WHERE SEQUENCE = 1 AND NOTES LIKE '%PRIORITY :%' I do
will Priority value/string always be terminated by a linefeed (ie CHAR(10)) ? Yes, it appears
You obviously dont want bell, tab, linefeed,carriage return, etc as part of your results - do they happen ? I am using a REPLACE to handle these issues, it is in my original select in my question.
will the priority value always be uppercase ie 'URGENT' or 'ROUTINE' or 'RETRO' - in which case, is there a need / reason for "else 'routine'" in the case statement - would prefer a warning.... It defaults to ROUTINE if it is anything else.
why not use WHERE SEQUENCE = 1 AND NOTES LIKE '%PRIORITY :%' I do
will Priority value/string always be terminated by a linefeed (ie CHAR(10)) ? Yes, it appears
You obviously dont want bell, tab, linefeed,carriage return, etc as part of your results - do they happen ? I am using a REPLACE to handle these issues, it is in my original select in my question.
will the priority value always be uppercase ie 'URGENT' or 'ROUTINE' or 'RETRO' - in which case, is there a need / reason for "else 'routine'" in the case statement - would prefer a warning.... It defaults to ROUTINE if it is anything else.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
While there are quite a few examples out there, to find the Microsoft Docs for "apply" it is part of the FROM clause : https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql#using-apply
Basically the APPLY allows us to use information from the left of the Apply in the right hand side of the Apply.
The right hand side can be a select (from current row as we are doing), a function, or a select from an entirely new datasource etc.
You ca use CROSS APPLY which is like an inner join, or OUTER APPLY which is like a left outer join.
My use of an alias "Priority(Priority_String_
Hope that explains the CROSS APPLY for you
Here is a technet article showing its use with a function : https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx
Basically the APPLY allows us to use information from the left of the Apply in the right hand side of the Apply.
The right hand side can be a select (from current row as we are doing), a function, or a select from an entirely new datasource etc.
You ca use CROSS APPLY which is like an inner join, or OUTER APPLY which is like a left outer join.
My use of an alias "Priority(Priority_String_
Hope that explains the CROSS APPLY for you
Here is a technet article showing its use with a function : https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx
ASKER
Hello,
Mark Wills, your solution worked. Using the other solutions, I had one record that did not retrieve correctly. Thank you so much for the explanation.
It was the select ltrim(substring(notes,char
AS start_of_priority_value
that I was able to use in the CROSS APPLY that yielded the correct value for all cases.
Mark Wills, your solution worked. Using the other solutions, I had one record that did not retrieve correctly. Thank you so much for the explanation.
It was the select ltrim(substring(notes,char
AS start_of_priority_value
that I was able to use in the CROSS APPLY that yielded the correct value for all cases.
ASKER
thank you to everyone for your help with this.
Happy to have been able to help :)
ASKER