March 6, 2019 at 7:49 am
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')
March 6, 2019 at 8:58 am
I don't know that it's better, but you could split the string on spaces, filter the results, and then recombine the string.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 6, 2019 at 10:09 am
WITH Base AS ( SELECT * FROM (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0) ) t(n) )
, Tally(n) AS (SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION) - 1 FROM Base AS a CROSS JOIN Base AS b CROSS JOIN Base AS c)
SELECT t.Disposition,
(
SELECT ln AS [text()]
FROM
(
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
March 6, 2019 at 10:16 am
Thanx for the reply but, that's a bit more involved than 8 replace statements.
March 6, 2019 at 10:30 am
NineIron - Wednesday, March 6, 2019 10:16 AMThanx for the reply but, that's a bit more involved than 8 replace statements.
You asked for better, not shorter.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 6, 2019 at 10:33 am
Touche. Thanx
March 14, 2019 at 4:34 am
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
March 14, 2019 at 4:42 am
Nice. Thanx.
March 15, 2019 at 4:08 pm
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
March 15, 2019 at 9:27 pm
A recursive CTE that looks for the first space in reverse order I suppose.
March 19, 2019 at 9:06 am
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