Parse string based on a list of values

  • Rather that use a bunch of replace functions, is there a better way to parse these values, CH,HC,MA,NH,OA,OTH,PSY,SNF, out of my data?

    create table #T


    Disposition varchar(100)


    insert into #T(Disposition) values('Andrew House Detoxificatio OTH')

    insert into #T(Disposition) values('Arbour Fuller Hospital PSY')

    insert into #T(Disposition) values('Arbour Hospital')

    insert into #T(Disposition) values('Baypointe Rehab NH')

    insert into #T(Disposition) values('Beth Israel Deaconess MedC OA')

    insert into #T(Disposition) values('Blue Hills Health and Reha NH')

    insert into #T(Disposition) values('Boston Medical Center OA')

    insert into #T(Disposition) values('Bournewood Hospital PSY')

    insert into #T(Disposition) values('BRAINTREE HOSPITAL CH')

    insert into #T(Disposition) values('Breamoor Rehabilitation NH')

    insert into #T(Disposition) values('Bridgewater Nursing Home NH')

    insert into #T(Disposition) values('Brigham and Womens Hospit OA')

    insert into #T(Disposition) values('Brighams and Womens Faulkn OA')

    insert into #T(Disposition) values('Brockton Dialysis HC')

    insert into #T(Disposition) values('Brockton Health Center')

    insert into #T(Disposition) values('Brockton Hospice Care HC')

    insert into #T(Disposition) values('Brockton Hospital OA')

    insert into #T(Disposition) values('Brockton Hospital Psych PSY')

    insert into #T(Disposition) values('Brockton Multi Service PSY')

    insert into #T(Disposition) values('Brockton VA OA')

    insert into #T(Disposition) values('Brockton VNA HC')

    insert into #T(Disposition) values('Castle Program at Brockton')

    insert into #T(Disposition) values('Champion Rehab Healthcare NH')

    insert into #T(Disposition) values('Childrens Hospital OA')

    insert into #T(Disposition) values('Colonial Rehab Nursing Cen NH')

    insert into #T(Disposition) values('Colony House Rehabilitatio NH')

    insert into #T(Disposition) values('Eloped OTH')

    insert into #T(Disposition) values('Emmanuel House Residence HC')

    insert into #T(Disposition) values('Expired')

    insert into #T(Disposition) values('Expired At Home')

    insert into #T(Disposition) values('Father Bill and Mainspring OTH')

    insert into #T(Disposition) values('Franciscan Hospital CH')

    insert into #T(Disposition) values('Guardian Center CH')

    insert into #T(Disposition) values('Heights Crossing HC')

    insert into #T(Disposition) values('High Point at Brockton')

    insert into #T(Disposition) values('High Point at Middleboro')

    insert into #T(Disposition) values('High Point at Plymouth')

    insert into #T(Disposition) values('Home (Self-Care)')

    insert into #T(Disposition) values('Home Health Service')

    insert into #T(Disposition) values('Hospice - Home')

    insert into #T(Disposition) values('Lahey Clinic Medical Center')

    insert into #T(Disposition) values('Law Enforcement Referral OTH')

    insert into #T(Disposition) values('Left Without Being Seen OTH')

    insert into #T(Disposition) values('Life Care Ctr W BrdgWater NH')

    insert into #T(Disposition) values('Massachusetts Eye and Ear')

    insert into #T(Disposition) values('Massachusetts General Hosp OA')

    insert into #T(Disposition) values('Mclean Hospital PSY')

    insert into #T(Disposition) values('New England Sinai Hospital CH')

    insert into #T(Disposition) values('New England Sinai Rehb Ctr NH')

    insert into #T(Disposition) values('Norcap Lodge PSY')

    insert into #T(Disposition) values('Norton Crisis Center')

    insert into #T(Disposition) values('Nursing Home Other')

    insert into #T(Disposition) values('Oakhill Nursing Rehab Ctr NH')

    insert into #T(Disposition) values('Old Colony Elder Services HC')

    insert into #T(Disposition) values('Other Acute Hospital OA')

    insert into #T(Disposition) values('Other Detoxification OTH')

    insert into #T(Disposition) values('Other Group Home')

    insert into #T(Disposition) values('Other Home Care Agency HC')

    insert into #T(Disposition) values('Other Psych Hospital')

    insert into #T(Disposition) values('Other SNF')

    insert into #T(Disposition) values('Pembroke Hospital')

    insert into #T(Disposition) values('Pocassett Mental Health Center')

    insert into #T(Disposition) values('Rhode Island Hospital')

    insert into #T(Disposition) values('Sachem Center for Hth Rehb NH')

    insert into #T(Disposition) values('Shelter OTH')

    insert into #T(Disposition) values('South Shore Hospital')

    insert into #T(Disposition) values('South Shore Rehabilitation OA')

    insert into #T(Disposition) values('Southcoast Behavioral Health')

    insert into #T(Disposition) values('Southeast Rehabilitation NH')

    insert into #T(Disposition) values('Spaulding Rehab Hospital CH')

    insert into #T(Disposition) values('ST Elizabeths Medical Center')

    insert into #T(Disposition) values('ST Josephs Manor NH')

    insert into #T(Disposition) values('Steward Carney Hospital')

    insert into #T(Disposition) values('Steward Good Samaritan Med Cen')

    insert into #T(Disposition) values('Steward Morton Med Center OA')

    insert into #T(Disposition) values('Taravista Behavioral Health')

    insert into #T(Disposition) values('Teen Challenge OTH')

    insert into #T(Disposition) values('Tufts Medical Center')

    insert into #T(Disposition) values('Webster Park NH')

    insert into #T(Disposition) values('West Acres Rehabilitation NH')

    insert into #T(Disposition) values('West Roxbury VA OA')

    insert into #T(Disposition) values('West View Rest Home MA')

  • I don't know that it's better, but you could split the string on spaces, filter the results, and then recombine the string.


    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • WITH Base AS ( SELECT * FROM (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0) ) t(n) )
    SELECT t.Disposition,
      SELECT ln AS [text()]
       SELECT SUBSTRING( t.Disposition, n + 1, LEAD(ta.n, 1, LEN(t.Disposition)) OVER(PARTITION BY t.Disposition ORDER BY ta.n) - n) AS ln
       FROM Tally ta
       WHERE ta.n <= LEN(t.Disposition)
        AND ( SUBSTRING(t.Disposition, ta.n, 1) = ''
          OR n = 0
      ) ta
      WHERE ta.ln NOT IN ('CH', 'HC', 'MA', 'NH', 'OA', 'OTH', 'PSY', 'SNF')
      FOR XML PATH(''), TYPE
     ).value('.', 'varchar(100)')
    FROM #T t
    ORDER BY t.Disposition

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Thanx for the reply but, that's a bit more involved than 8 replace statements.

  • NineIron - Wednesday, March 6, 2019 10:16 AM

    Thanx for the reply but, that's a bit more involved than 8 replace statements.

    You asked for better, not shorter.


    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Touche. Thanx

  • Here is the shorter version, but still better:

    create table #Location

    ShortCode varchar(4)
    insert into #Location(ShortCode) values('HC')
    insert into #Location(ShortCode) values('CH')
    insert into #Location(ShortCode) values('MA')
    insert into #Location(ShortCode) values('NH')
    insert into #Location(ShortCode) values('OA')
    insert into #Location(ShortCode) values('OTH')
    insert into #Location(ShortCode) values('PSY')
    insert into #Location(ShortCode) values('SNF')

    SELECT *, substring(t.Disposition, 1, LEN(t.disposition) - isnull(LEN(L.ShortCode), 0))
    FROM #T t
    left join #Location L on RIGHT(t.Disposition, LEN (L.ShortCode) + 1) = ' ' + L.ShortCode

    Code for TallyGenerator

  • Nice. Thanx.

  • One more variation:

    select #t.*
            , case when right(Disposition,nullif(charindex(space(1),reverse(disposition)),0)-1)
                        in ('CH', 'HC', 'MA', 'NH', 'OA', 'OTH', 'PSY', 'SNF')
                     then right(Disposition,nullif(charindex(space(1),reverse(disposition)),0)-1) end as ShortCode
    into #r2
    from #t


    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • A recursive CTE that looks for the first space in reverse order I suppose.

  • Not recursive at all, just a cte.    But yes, it finds the first space from the right to pull the strings to be tested.


    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 11 posts - 1 through 10 (of 10 total)

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