parsing data out of a single data point

Question

I have a nasty data set:

+----+------------------------------------------------------------------------------+
| PK |                                 Medications                                  |
+----+------------------------------------------------------------------------------+
|  1 | NAPROXEN, neurontin, DOCUSATE, HYDROCODONE, BACLOFEN, advil                  |
|  2 | celexa, lortab, lyrica, ambien, xanax                                        |
|  3 | adipex                                                                       |
|  4 | opana, roxicodone                                                            |
|  5 | adderall                                                                     |
|  6 | hydrocodone/apap                                                             |
|  7 | NEXIUM, METOPROLOL, lipitor, VERAPAMIL, ASPIRIN, WARFARIN, ambien            |
|  8 | prozac                                                                       |
|  9 | flexeril                                                                     |
| 10 | soma, LITHIUM, MULTI-VITAMIN, fentanyl patch, percocet, PROPANOLOL, tegretol |
+----+------------------------------------------------------------------------------+

Please keep in mind that this is just 2 columns.

What I would like to return is simply a 1 column list of distinct medications across the entire dataset:

NAPROXEN
 neurontin
 DOCUSATE
 HYDROCODONE
 BACLOFEN
 advil
celexa
 lortab
 lyrica
 ambien
 xanax
adipex
opana

What is the best way to go about this?

Thank you so much for your guidance.

You must split the concatenated result and return it back. Take a look at http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco — Jahan Zinedine, Nov 30 '12 at 00:04

score 4 · Accepted Answer · edited Oct 29 '18 at 12:47

DECLARE @Medications TABLE
(
     PK BIGINT PRIMARY KEY IDENTITY(1,1)
    ,Medications NVARCHAR(4000)
)

/* Populate the table with example records*/
INSERT INTO @Medications(Medications) 
VALUES ('NAPROXEN, neurontin, DOCUSATE, HYDROCODONE, BACLOFEN, advil')
      ,('celexa, lortab, lyrica, ambien, xanax   ')
      ,('adipex')
      ,('opana roxicodone')
      ,('adderall')
      ,('hydrocodone/apap')
      ,('NEXIUM,METOPROLOL,lipitor,VERAPAMIL,ASPIRIN,WARFARIN,ambien')
      ,('prozac')
      ,('flexeril')
      ,('soma,LITHIUM,MULTI-VITAMIN,fentanyl patch,percocet,PROPANOLOL,tegretol');

Option 1

select distinct Medication = ltrim(rtrim(O.splitdata ))
from
(
select *,
cast('<X>'+replace(F.Medications,',','</X><X>')+'</X>' as XML) as xmlfilter
from @Medications F
)F1
cross apply
( 
 select fdata.D.value('.','varchar(50)') as splitdata 
 from f1.xmlfilter.nodes('X') as fdata(D)) O
where O.splitdata <> ''
group by O.splitdata

Option 2

SELECT DISTINCT display_term
FROM @Medications
CROSS APPLY sys.dm_fts_parser('"' + Medications + '"', 1033, 0,0)
where display_term NOT LIKE 'nn%'

Option 3

SELECT  DISTINCT
    LTRIM(RTRIM(SUBSTRING(Medications, Number ,CHARINDEX(',', Medications + ',', Number ) - Number))) AS Medication 
FROM @Medications 
JOIN master..spt_values ON Number <= DATALENGTH(Medications) + 1  AND type='P'
AND SUBSTRING(',' + Medications, Number , 1) = ','

score 3 · Answer 2 · answered Nov 30 '12 at 07:55

This might be the solution you are looking for:

;WITH SourceDate(PK,MedicationsXML) AS
(
    SELECT PK
          ,CAST(N'<r><![CDATA[' + REPLACE(Medications, ',', ']]></r><r><![CDATA[') + ']]></r>' AS XML)
    FROM @Medications
)
SELECT DISTINCT Medicament
FROM SourceDate
CROSS APPLY (SELECT DISTINCT RTRIM(LTRIM(Tbl.Col.value('.', 'nvarchar(250)'))) AS Medicament FROM MedicationsXML.nodes('//r') Tbl(Col)) AS List

And this is full working example (I add duplicated records to show that it is returning only the distinct value:

SET NOCOUNT ON
GO

    /* Declare table that holds the data*/
    DECLARE @Medications TABLE
    (
         PK BIGINT PRIMARY KEY IDENTITY(1,1)
        ,Medications NVARCHAR(4000)
    )

    /* Populate the table with exmaple records*/
    INSERT INTO @Medications(Medications)
    VALUES ('NAPROXEN, neurontin, DOCUSATE, HYDROCODONE, BACLOFEN, advil')
          ,('celexa, lortab, lyrica, ambien, xanax   ')
          ,('adipex')
          ,('opana, roxicodone')
          ,('adderall')
          ,('hydrocodone/apap')
          ,('NEXIUM, METOPROLOL, lipitor, VERAPAMIL, ASPIRIN, WARFARIN, ambien')
          ,('prozac')
          ,('flexeril')
          ,('soma, LITHIUM, MULTI-VITAMIN, fentanyl patch, percocet, PROPANOLOL, tegretol')
          ,('NAPROXEN, neurontin, DOCUSATE, HYDROCODONE, BACLOFEN, advil')
          ,('celexa, lortab, lyrica, ambien, xanax   ')
          ,('adipex')
          ,('opana, roxicodone')
          ,('adderall')
          ,('hydrocodone/apap')
          ,('NEXIUM, METOPROLOL, lipitor, VERAPAMIL, ASPIRIN, WARFARIN, ambien')
          ,('prozac')
          ,('flexeril')
          ,('soma, LITHIUM, MULTI-VITAMIN, fentanyl patch, percocet, PROPANOLOL, tegretol')

    ;WITH SourceDate(PK,MedicationsXML) AS
    (
        SELECT PK
              ,CAST(N'<r><![CDATA[' + REPLACE(Medications, ',', ']]></r><r><![CDATA[') + ']]></r>' AS XML)
        FROM @Medications
    )
    SELECT DISTINCT Medicament
    FROM SourceDate
    CROSS APPLY (SELECT DISTINCT RTRIM(LTRIM(Tbl.Col.value('.', 'nvarchar(250)'))) AS Medicament FROM MedicationsXML.nodes('//r') Tbl(Col)) AS List


SET NOCOUNT OFF
GO

Feel free to ask any questions.

score 2 · Answer 3 · answered Nov 30 '12 at 00:25

if you can output it to awk

outputcommand | awk 'BEGIN{FS="|"}{print $3}'|awk 'BEGIN{RS=","}{print $0}'

if its a simple text file you can use cat filename for the outputcommand or:

awk 'BEGIN{FS="|"}{print $3}' some_filename |awk 'BEGIN{RS=","}{print $0}'

parsing data out of a single data point

3 Answers3