Extracting values from a single column

jollyegeorge · September 22, 2016, 5:52pm

I would like to extract only the first word (till the 4mg, there will always be a number and unit) in the below column and also the characters after 'Reason for Bridging:' till 'Quantity to dispense:'

Here is the sample data

yosiasz · September 22, 2016, 5:57pm

what are all the possible unit values other than mg?

jollyegeorge · September 22, 2016, 5:58pm

ml,g, But there will be a number before that, I do not need that number to be included,
just the medication name only

yosiasz · September 22, 2016, 6:13pm

declare @pills table(prescription varchar(max))

insert into @pills
select 'zofran 4 mg SL q6h PRN Reason for bridging: No timely access to a community pharmacy Quantity to dispense: 4'
union
select 'Ondansetron 4 mg SL q8h PRN Reason for bridging: Funding assistance not yet in place to support purchase of medications Quantity to dispense: 4 tabs'

select prescription, left(prescription, patindex('%[0-9]%',prescription) -1), patindex('%[0-9]%',prescription) from @pills

ScottPletcher · September 22, 2016, 7:10pm

SELECT *,
    RTRIM(LEFT(dispensing, end_of_drug_name)) AS drug_name,
    LTRIM(RTRIM(SUBSTRING(dispensing, start_of_reason, end_of_reason - start_of_reason + 1))) AS reason
FROM (
    VALUES('zofran 4 mg SL q6h PRN Reason for bridging: No timely access to a community pharmacy Quantity to dispense: 4'),
          ('Ondansetron 4 mg SL q8h PRN Reason for bridging: Funding assistance not yet in place to support purchase of medications Quantity to dispense: 4 tabs'),
          ('drug-name-only 5mg'),
          ('4nodrug but reason for bridging: any_reason_goes_here')
) AS test_data(dispensing)
CROSS APPLY (
    SELECT NULLIF(PATINDEX('%[0-9]%', dispensing), 0) - 1 AS end_of_drug_name,
        NULLIF(CHARINDEX('Reason for bridging:', dispensing), 0) + LEN('Reason for bridging:') AS start_of_reason
) AS ca1
CROSS APPLY (
    SELECT ISNULL(NULLIF(CHARINDEX('Quantity to disp', dispensing, start_of_reason), 0), LEN(dispensing)) AS end_of_reason
) AS ca2

jollyegeorge · September 22, 2016, 8:56pm

Trying, sorry to mention the Sql I am modifying is below,

SELECT SummaryLine from cv3order where name like 'bridging medication generic%'

the SummaryLine column is CHAR(2000)

and the output is here, which I need to split into two columns 'Medication name' and 'Reason for Bridging'

jotorre_riversidedpss.org · September 23, 2016, 12:32am

Try:

DECLARE @pills table
   (
     SummaryLine varchar(MAX)
   );

INSERT INTO @pills
   SELECT
         'zofran 4 mg SL q6h PRN Reason for bridging: No timely access to a community pharmacy Quantity to dispense: 4'
   UNION
   SELECT
         'Ondansetron 4 mg SL q8h PRN Reason for bridging: Funding assistance not yet in place to support purchase of medications Quantity to dispense: 4 tabs';

SELECT
      Left(SummaryLine, CharIndex(' ', SummaryLine)-1) Drug
   FROM
      @pills;

It's a little simpler.

jollyegeorge · September 23, 2016, 1:49pm

I cannot give the values, the table contains more than 20000 rows in summary column

jollyegeorge · September 23, 2016, 2:25pm

Getting the following error as I try this,

(389055 row(s) affected)
Msg 537, Level 16, State 3, Line 9
Invalid length parameter passed to the LEFT or SUBSTRING function.

DECLARE @pills table
(
SummaryLine varchar(MAX)
);
INSERT INTO @pills
SELECT
summaryline from CV3Order

SELECT
Left(SummaryLine, CharIndex(' ', SummaryLine)-1) Drug
FROM
@pills;

jollyegeorge · September 23, 2016, 3:06pm

Thank you very much Scott,

This is actually working, except I need only 2 columns Drug_Name and Reason

jotorre_riversidedpss.org · September 23, 2016, 4:18pm

Try something like:

DECLARE @pills table
   (
     SummaryLine varchar(MAX)
   );

INSERT INTO @pills
   SELECT
         'zofran 4 mg SL q6h PRN Reason for bridging: No timely access to a community pharmacy Quantity to dispense: 4'
   UNION
   SELECT
         'Ondansetron 4 mg SL q8h PRN Reason for bridging: Funding assistance not yet in place to support purchase of medications Quantity to dispense: 4 tabs';

SELECT
      Left(SummaryLine, CharIndex(' ', SummaryLine)-1) Drug, Substring(SummaryLine, CharIndex('Reason for bridging:', SummaryLine)+21,(CharIndex('Quantity to dispense:', SummaryLine)-1)-CharIndex('Reason for bridging:', SummaryLine)-21) [Reason for bridging] 
   FROM
      @pills;

yosiasz · September 23, 2016, 4:36pm

declare @pills table(prescription varchar(max))

insert into @pills
select replace(replace('zofran 4 mg SL q6h PRN Reason for bridging: No timely access to a community pharmacy Quantity to dispense: 4', 'Quantity to dispense: ', '['), 'Reason for bridging: ','{')
union
select replace(replace('Ondansetron 4 mg SL q8h PRN Reason for bridging: Funding assistance not yet in place to support purchase of medications Quantity to dispense: 4 tabs', 'Quantity to dispense: ', '['), 'Reason for bridging: ','{')

select prescription,
left(prescription, patindex('%[0-9]%',prescription) -1) as drug,
substring(prescription,charindex('{', prescription, 0) + 1, charindex('[', prescription, 0) - charindex('{', prescription, 0) -2) Reason
from @pills

ScottPletcher · September 23, 2016, 5:23pm

Sorry, I forgot to remove the "*" from the SELECT. That was basically just for my testing and to demonstrate what the code was doing:

SELECT 
    RTRIM(LEFT(dispensing, end_of_drug_name)) AS [Medication name],
    LTRIM(RTRIM(SUBSTRING(dispensing, start_of_reason, 
        end_of_reason - start_of_reason + 1))) AS [Reason for Bridging]
FROM your_table_name
CROSS APPLY (
    SELECT NULLIF(PATINDEX('%[0-9]%', dispensing), 0) - 1 AS end_of_drug_name,
        NULLIF(CHARINDEX('Reason for bridging:', dispensing), 0) + 
            LEN('Reason for bridging:') AS start_of_reason
) AS ca1
CROSS APPLY (
    SELECT ISNULL(NULLIF(CHARINDEX('Quantity to disp', 
        dispensing, start_of_reason), 0), LEN(dispensing)) AS end_of_reason
) AS ca2

jollyegeorge · September 23, 2016, 8:26pm

It did not work when I gave the where clause to the table,

SELECT
RTRIM(LEFT(Summaryline, end_of_drug_name)) AS [Medication name],
LTRIM(RTRIM(SUBSTRING(Summaryline, start_of_reason,
end_of_reason - start_of_reason + 1))) AS [Reason for Bridging]
FROM CV3Order where name like ‘Bridging%’
CROSS APPLY (
SELECT NULLIF(PATINDEX('%[0-9]%', Summaryline), 0) - 1 AS end_of_drug_name,
NULLIF(CHARINDEX('Reason for bridging:', Summaryline), 0) +
LEN('Reason for bridging:') AS start_of_reason
) AS ca1
CROSS APPLY (
SELECT ISNULL(NULLIF(CHARINDEX('Quantity to disp',
Summaryline, start_of_reason), 0), LEN(Summaryline)) AS end_of_reason
) AS ca2

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'CROSS'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'AS'.

ScottPletcher · September 23, 2016, 8:40pm

CROSS APPLYs are kinda like JOINs, so the WHERE goes after all the CAs:

SELECT
RTRIM(LEFT(Summaryline, end_of_drug_name)) AS [Medication name],
LTRIM(RTRIM(SUBSTRING(Summaryline, start_of_reason,
end_of_reason - start_of_reason + 1))) AS [Reason for Bridging]
FROM CV3Order
CROSS APPLY (
SELECT NULLIF(PATINDEX('%[0-9]%', Summaryline), 0) - 1 AS end_of_drug_name,
NULLIF(CHARINDEX('Reason for bridging:', Summaryline), 0) +
LEN('Reason for bridging:') AS start_of_reason
) AS ca1
CROSS APPLY (
SELECT ISNULL(NULLIF(CHARINDEX('Quantity to disp',
Summaryline, start_of_reason), 0), LEN(Summaryline)) AS end_of_reason
) AS ca2
WHERE name like ‘Bridging%’

Btw, did you mean '**%**Bridging%'?

jollyegeorge · September 23, 2016, 8:58pm

Thank you very much, (my ignorance on this)

here is the result of the query which is correctly coming, But There is a 'Q' is coming at the end which is the next field 'Quantity' (do not need) This is after a space.

jotorre_riversidedpss.org · September 23, 2016, 9:04pm

Did you try mine from the post above. It worked with the sample rows. BTW, it's a really bad idea to store data this way. This schema needs to be normalized.

ScottPletcher · September 23, 2016, 9:12pm

I need to remove the "+ 1" from the Reason extract code.

SELECT 
    RTRIM(LEFT(dispensing, end_of_drug_name)) AS [Medication name],
    LTRIM(RTRIM(SUBSTRING(dispensing, start_of_reason, 
        end_of_reason - start_of_reason /*+ 1*/))) AS [Reason for Bridging]
FROM your_table_name
CROSS APPLY (
    SELECT NULLIF(PATINDEX('%[0-9]%', dispensing), 0) - 1 AS end_of_drug_name,
        NULLIF(CHARINDEX('Reason for bridging:', dispensing), 0) + 
            LEN('Reason for bridging:') AS start_of_reason
) AS ca1
CROSS APPLY (
    SELECT ISNULL(NULLIF(CHARINDEX('Quantity to disp', 
        dispensing, start_of_reason), 0), LEN(dispensing)) AS end_of_reason
) AS ca2

jollyegeorge · September 23, 2016, 9:25pm

Thanks a lot

jollyegeorge · September 27, 2016, 6:50pm

Is there a way to consolidate below 2 queries so that I get both Medication name and Bridging reason, Somehow my efforts is not successful

thanks

SELECT
ORD.Name,OUD.UserDataCode,OUD.Value [Medication Name]
FROM CV3ORDER ORD

JOIN CV3OrderUserData  OUD
ON OUD.OrderGUID = Ord.GUID 
AND OUD.ClientGUID = Ord.ClientGUID 
AND OUD.UserDataCode  = 'RX_BridgingMedicationName'

SELECT
ORD.Name,OUD.UserDataCode,OUD.Value [Bridging Reason]
FROM CV3ORDER ORD

JOIN CV3OrderUserData  OUD
ON OUD.OrderGUID = Ord.GUID 
AND OUD.ClientGUID = Ord.ClientGUID 
AND OUD.UserDataCode = 'RX_Bridgingreason'