Post reply

how to substring chunks of a string starting at different positions?

  • June 26, 2013 at 1:36 pm

    #275602

    i am spinning my wheels....

    i need to extract a distinct string from the first character up to the position where there is a number and then what ever, if anything follows the number, but cannot seem to get to it.

    Even though there are two Abilifys and six ACETAMINOPHENs, i only need it once without the dosages or strengths, but i need the type in a new column, so if its a TABLET or CAPLET, that should also be extracted, but placed in a new column.

    Further, idk if ACETAZOLAMID is a misspelling of ACETAZOLAMIDE, but i am guessing distinct will return them both.

    use tempdb

    create table #drugnames(drugname varchar(50))

    insert #drugnames

    select'10 SERIES BP MIS MONITOR'UNION ALL

    select'ABILIFY 20 MG TABLET'UNION ALL

    select'ABILIFY 5 MG TABLET'UNION ALL

    select'ACARBOSE TAB 50MG'UNION ALL

    select'ACCOLATE 20 MG TABLET'UNION ALL

    select'ACCUNEB 0.63 MG/3 ML INH SOLN'UNION ALL

    select'ACCUSURE INSULIN SYRN 0.5 ML'UNION ALL

    select'ACE ELASTIC BANDAGE 3"'UNION ALL

    select'ACETAMIN SUP 325MG'UNION ALL

    select'ACETAMINOPHEN 100 MG/ML DROP'UNION ALL

    select'ACETAMINOPHEN 120 MG SUPPOS'UNION ALL

    select'ACETAMINOPHEN 160 MG/5 ML ELIX'UNION ALL

    select'ACETAMINOPHEN 325 MG TABLET'UNION ALL

    select'ACETAMINOPHEN 500 MG CAPLET'UNION ALL

    select'ACETAMINOPHEN 650 MG/20.3 ML'UNION ALL

    select'ACETAMINOPHEN/COD #3 TABLET'UNION ALL

    select'ACETAZOLAMID TAB 250MG'UNION ALL

    select'ACETAZOLAMIDE 250 MG TABLET'UNION ALL

    select'ACETIC ACID 0.25% IRRIG SOLN'UNION ALL

    select'ACID CONTROL 75 MG TABLET'

    --select drugname from #drugnames

    so for example, my limits are reached here...

    select distinct substring(drugname, 1, charindex(' ',drugname)-1) from #drugnames

    drop table #drugnames

    but that doesnt get it because first of all it returns only ACE, and i need ACE ELASTIC BANDAGE...but in the case of ACETAMINOPHEN i need

    ACETAMINOPHEN DROP,

    ACETAMINOPHEN SUPPOS,

    ACETAMINOPHEN TABLET,

    ACETAMINOPHEN CAPLET,

    ACETAMINOPHEN ELIX and

    ACETAMINOPHEN ML,

    but i only need those suffixes once.

    how can i get from here to there?

    thanks very much

  • June 26, 2013 at 1:46 pm

    #1627716

    drew how about using patindex to find the first [0-9] match?

    this gets us kinda close, i think, except for that "10 SERIES BP MIS MONITOR"

    select

    patindex('%[0-9]%',drugname ) -1,

    SUBSTRING(drugname, 1, patindex('%[0-9]%',drugname) -1),

    SUBSTRING(drugname,patindex('%[0-9]%',drugname) -1,50),

    SUBSTRING(REVERSE(drugname),1,CHARINDEX(' ',REVERSE(drugname))),

    REVERSE(SUBSTRING(REVERSE(drugname),1,CHARINDEX(' ',REVERSE(drugname)))),

    drugname

    from #drugnames

    EIDT:

    added code for best guess on extracting suffix based on reverseing functions:

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 27, 2013 at 5:13 am

    #1627912

    Brilliant Holmes!

    I will look into some other treatment for strings starting with numbers, but patindex with numeric wildcards was the correct approach, not charindex.

    Thanks very much for your help

    drew

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply