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.

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%' 

Open in new window


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.
newtoperlpgmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
>> whatever text is between PRIORITY :  and SYMPTOMS :

In other words, you want to find the value of PRIORITY? Does the real text contain line breaks like in the example?

SYMPTOMS : NOTES ATTACHED    <=== line break
TREATMENT PLAN :                          <=== line break
PRIORITY : URGENT
0
newtoperlpgmAuthor Commented:
yes,  I do want to find the Priority.  Also,there are line breaks.  Thank you.
0
Scott PletcherSenior DBACommented:
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%'
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

_agx_Commented:
(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
1
newtoperlpgmAuthor Commented:
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
0
Scott PletcherSenior DBACommented:
Actually, my code is written to insure that it doesn't matter what precedes or follows 'PRIORITY :', including no other entry at all (that's why I add a "CHAR(10)" to the end of NOTES, to make sure that delimiter is present if PRIORITY happened to be the last entry in that line).

Below is my original code (with adjustments from _agx_ (@_agx_ THANKS!), with your test data above.

Since you've refused to provide actual, consumable sample data, I have no idea if the data structure matches the data in  your table(s) or not.

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 (
    SELECT 100 AS AUTHNO, 1 AS SEQUENCE, '
TREATMENT PLAN : REQUESTING TEXT, WE WILL BE GOING OVER DI
AGNOSTIC TESTING- OKAY PER  MANAGER.
PRIORITY : ROUTINE
SYMPTOMS : PLEASE SEE ATTACHED- TEXT TEST
' AS NOTES        
) AS sample_data
CROSS APPLY (
    SELECT CHARINDEX('PRIORITY :', NOTES) + 10 AS start_of_priority_value /* added space and increased to 10, as _agx_ noted */
) AS alias1
WHERE SEQUENCE = '1' AND NOTES LIKE '%PRIORITY%'

I realized later I should have added a replacement for CHAR(13), just in case it's CR+LF rather than just LF.  And an LTRIM to remove leading space(s).

SELECT
AUTHNO,
LTRIM(REPLACE(SUBSTRING(NOTES, start_of_priority_value, CHARINDEX(CHAR(10), NOTES + CHAR(10),
    start_of_priority_value) - start_of_priority_value), CHAR(13), '')) AS PRIORITY
FROM (
    SELECT 100 AS AUTHNO, 1 AS SEQUENCE, '
TREATMENT PLAN : REQUESTING TEXT, WE WILL BE GOING OVER DI
AGNOSTIC TESTING- OKAY PER  MANAGER.
PRIORITY : ROUTINE
SYMPTOMS : PLEASE SEE ATTACHED- TEXT TEST
' AS NOTES        
) AS sample_data
CROSS APPLY (
    SELECT CHARINDEX('PRIORITY :', NOTES) + 10 AS start_of_priority_value
) AS alias1
WHERE SEQUENCE = '1' AND NOTES LIKE '%PRIORITY%'
1
Mark WillsTopic AdvisorCommented:
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....

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%'

Open in new window

1
newtoperlpgmAuthor Commented:
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.
0
Mark WillsTopic AdvisorCommented:
Thanks for your responses, in which case please try :
select authno  
      ,CASE when Priority_String_Extract like 'urgent%' then 'URGENT'
            when Priority_String_Extract like 'retro%' then 'RETRO'
       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 :%'

Open in new window

2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
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_Extract)" has a table alias of Priority with a named column Priority_String_Extract

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
1
newtoperlpgmAuthor Commented:
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,charindex('PRIORITY :',Notes)+10,7))
AS start_of_priority_value
that I was able to use in the CROSS APPLY that yielded the correct value for all cases.
0
newtoperlpgmAuthor Commented:
thank you to everyone for your help with this.
0
Mark WillsTopic AdvisorCommented:
Happy to have been able to help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.