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
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
what are all the possible unit values other than mg?
ml,g, But there will be a number before that, I do not need that number to be included,
just the medication name only
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
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
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'
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.
I cannot give the values, the table contains more than 20000 rows in summary column
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;
Thank you very much Scott,
This is actually working, except I need only 2 columns Drug_Name and Reason
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;
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
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
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'.
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%'?
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.
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.
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
Thanks a lot
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'