SQLTeam.com | Weblogs | Forums

Extracting values from a single column


#1

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


#2

what are all the possible unit values other than mg?


#3

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


#4

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


#5
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

#6

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'


#7

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.


#8

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


#9

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;


#10

Thank you very much Scott,

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


#11

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;

#12

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


#13

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

#14

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


#15

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


#16

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.


#17

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.


#18

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

#19

Thanks a lot


#20

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'