asked on

Right Trim or Left Trim a column in SQL up to and including the comma separator

I have read through many explanations and cannot find one that helps me with this. Hopefully others will find the solution useful also.

I have a field with lastname, firstname. I need to split these into two separate fields and include consideration for null values.
Null values should display as blank.
The fields must be 15 spaces for last name, 14 spaces for first name, where the name starts at the left of the column and leave empty spaces at the end.

Example:
Smithsonian,Charles would end up as
[Smithsonian ]
[Charles ]
disregard the [ ] those are for explanation only.

Also, when I add this to my existing query I get this error message.
"Msg 116, Level 16, State 1, Line 25
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

I am using SQL Server 2008 and VS 2013.
This will one field in the entire query that will eventually end up as a stored procedure to be used in Visual Studio.
I have attached my entire query as I now understand that helps.
PLEASE disregard my attempt to do this inside the existing query, it does not work like it is supposed to. (Column SpouseLN)
metriq_for_EE.sql

Pawan Kumar

Try this

--
DECLARE @ AS VARCHAR(MAX) = 'Smithsonian,Charles '
SELECT 
SUBSTRING((@),CHARINDEX(',',@)+1,20) AS PatientLast
,SUBSTRING((@),1,CHARINDEX(',',@)-1) AS PatientFirst
--

Open in new window

O/p
----------
PatientLast PatientFirst
Charles Smithsonian

Try

SELECT 
  SUBSTRING('',1,5) AS Hospital_ID  --no hospital ids have 5 or less characters
 ,SUBSTRING((@),CHARINDEX(',',@)+1,20) AS PatientLast
 ,SUBSTRING((@),1,CHARINDEX(',',@)-1) AS PatientFirst
 ,SUBSTRING(ISNULL(p.PAT_MIDDLE_NAME,''),1,1) AS PatientMI
 ,SUBSTRING(CAST(p.PAT_MRN_ID AS VARCHAR),1,11) AS Medical_Record_Number
 ,SUBSTRING(CAST(REPLACE(p.SSN,'-','') AS VARCHAR),1,9) AS SSN
 ,SUBSTRING(convert(varchar, p.BIRTH_DATE, 112),1,10) AS Birthdate
 ,SUBSTRING('',1,4)AS ICD03
 ,SUBSTRING('',1,5) AS ICD9
 ,DATELASTCONTACT = (SELECT max(DISCH_date_time) FROM clarity.dbo.HSP_ACCOUNT hsp WHERE hsp.HSP_ACCOUNT_ID = pe.HSP_ACCOUNT_ID)
 ,CASE WHEN county.ABBR IS NULL THEN '  ' ELSE SUBSTRING(county.ABBR,1,3) END AS COUNTY
 ,SUBSTRING(p.ADD_LINE_1,1,38) AS Addr
 ,SUBSTRING(p.CITY,1,15) AS City
 ,SUBSTRING(Patient_State.NAME,1,2) AS State
 ,SUBSTRING(CAST(p.ZIP AS VARCHAR),1,5) AS Zipcode
 ,SUBSTRING('',1,4) AS Zip2
 ,SUBSTRING(CAST(REPLACE(p.HOME_PHONE,'-','') AS VARCHAR),1,10) AS Telephone
 ,SUBSTRING('',1,15) AS MaidenNm
 
 --,SpouseLN = (select ec.emerg_name, SUBSTRING(RTRIM(ec.emerg_name),2, LEN(RTRIM(ec.emerg_name))) from emergency_contacts ec
--		INNER JOIN ZC_EMERG_PAT_REL ZC ON ec.EMERG_PAT_REL_C = zc.EMERG_PAT_REL_C
--		where zc.EMERG_PAT_REL_C = 17)

 ,SUBSTRING('',1,15) AS SpouseLN
 ,SUBSTRING('',1,14) AS SpouseFN
 ,SUBSTRING('',1,3) AS PlaceOfBirth
 ,SUBSTRING('',1,1) AS Autopsy
,CASE WHEN gender.RCPT_MEM_SEX_C = 1 THEN 2 WHEN gender.RCPT_MEM_SEX_C = 2 THEN 1 ELSE gender.RCPT_MEM_SEX_C END AS Sex 

,CASE
WHEN prace.PATIENT_RACE_C is null THEN '  ' 
--when prace.PATIENT_RACE_C = 1 then 1
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '1' THEN '1' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '2' THEN '2' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '3' THEN '3'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '4' THEN '96'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '5' THEN '7'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '6' THEN '98'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '7' THEN '99'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '8' THEN '99'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '9' THEN '4'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '10' THEN '6'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '11' THEN '5' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '12' THEN '8' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '13' THEN '10' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '14' THEN '15' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '15' THEN '7' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '16' and prace.NAME = 'Guamanian' then '22'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '16' and prace.NAME = 'Chamorro' then '21'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '17' THEN '27' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '18' THEN '97' 
END AS RACE

,CASE
WHEN p.ETHNIC_GROUP_C = 1 THEN 0
WHEN p.ETHNIC_GROUP_C = 5 THEN 1
WHEN p.ETHNIC_GROUP_C = 6 THEN 2
WHEN p.ETHNIC_GROUP_C = 7 THEN 3
WHEN p.ETHNIC_GROUP_C = 8 THEN 4
WHEN p.ETHNIC_GROUP_C = 2 THEN 1
ELSE 9
END AS SpanishOriginEthnicity

,CASE
WHEN p.MARITAL_STATUS_C is null THEN '?' 
WHEN SUBSTRING(CAST(p.MARITAL_STATUS_C AS VARCHAR),1,1) = '6' THEN '9' 
ELSE SUBSTRING(CAST(p.MARITAL_STATUS_C AS VARCHAR),1,1)
END AS MaritalStatus

,SUBSTRING('',1,255) AS OTHER
,SUBSTRING(comb.REF_BILL_CODE,1,7) as ICD10

 FROM clarity.dbo.PAT_ENC						pe
 inner join CLARITY.DBO.HSP_ACCOUNT				ha ON pe.HSP_ACCOUNT_ID=ha.HSP_ACCOUNT_ID 
 INNER JOIN clarity.dbo.PATIENT					p ON pe.PAT_ID=p.PAT_ID
 INNER JOIN clarity.dbo.PAT_ENC_DX dx ON		dx.PAT_ENC_CSN_ID=pe.PAT_ENC_CSN_ID
 LEFT OUTER JOIN clarity.dbo.CLARITY_EDG		edg ON edg.DX_ID=dx.DX_ID
 LEFT OUTER JOIN clarity.dbo.V_CODING_COMBINED_DX_LIST comb ON comb.HSP_ACCOUNT_ID=pe.HSP_ACCOUNT_ID
 LEFT OUTER JOIN clarity.dbo.ACCOUNT			acct ON pe.ACCOUNT_ID=acct.ACCOUNT_ID
 LEFT OUTER JOIN clarity.dbo.CLARITY_EEP		eep ON acct.EMPLOYER_ID=eep.EMPLOYER_ID
 LEFT OUTER JOIN clarity.dbo.ZC_EMPY_STAT		ZC_EMPY_STAT ON acct.EMPY_STAT_C=ZC_EMPY_STAT.EMPY_STAT_C
 LEFT OUTER JOIN clarity.dbo.ZC_STATE			Guar_State ON acct.STATE_C=Guar_State.STATE_C
 LEFT OUTER JOIN clarity.dbo.ACCT_GUAR_PAT_INFO ACCT_GUAR_PAT_INFO ON acct.ACCOUNT_ID=ACCT_GUAR_PAT_INFO.ACCOUNT_ID
 LEFT OUTER JOIN clarity.dbo.ZC_GUAR_REL_TO_PAT ZC_GUAR_REL_TO_PAT ON ACCT_GUAR_PAT_INFO.GUAR_REL_TO_PAT_C=ZC_GUAR_REL_TO_PAT.GUAR_REL_TO_PAT_C
 LEFT OUTER JOIN clarity.dbo.ZC_STATE			Patient_State ON p.STATE_C=Patient_State.STATE_C
 LEFT OUTER JOIN clarity.dbo.ZC_SEX				gender ON p.SEX_C=gender.RCPT_MEM_SEX_C
 LEFT OUTER JOIN clarity.dbo.CLARITY_EEP		eep_Patient ON p.EMPLOYER_ID=eep_Patient.EMPLOYER_ID
 LEFT OUTER JOIN clarity.dbo.ZC_EMPY_STATUS		ZC_EMPY_STATUS ON p.EMPY_STATUS_C=ZC_EMPY_STATUS.EMPY_STATUS_C
 LEFT OUTER JOIN clarity.dbo.CLARITY_DEP		dep ON dep.DEPARTMENT_ID=pe.DEPARTMENT_ID
 LEFT OUTER JOIN clarity.dbo.ZC_APPT_STATUS		apptstatus ON apptstatus.APPT_STATUS_C=pe.APPT_STATUS_C
 LEFT OUTER JOIN clarity.dbo.PATIENT_RACE		race ON race.PAT_ID=p.PAT_ID
 LEFT OUTER JOIN clarity.dbo.ZC_PATIENT_RACE	prace ON prace.PATIENT_RACE_C=race.PATIENT_RACE_C
 --LEFT OUTER JOIN clarity.dbo.CLARITY_PRC		prc ON prc.PRC_ID=pe.APPT_PRC_ID
 --LEFT OUTER JOIN clarity.dbo.ZC_LANGUAGE		lang ON lang.LANGUAGE_C=p.LANGUAGE_C
 --LEFT OUTER JOIN clarity.dbo.ZC_MARITAL_STATUS	ms ON ms.MARITAL_STATUS_C=p.MARITAL_STATUS_C
 LEFT OUTER JOIN clarity.dbo.ZC_COUNTY			county ON p.COUNTY_C=county.COUNTY_C
 --LEFT OUTER JOIN clarity.dbo.ZC_ACCT_BASECLS_HA basecls ON basecls.ACCT_BASECLS_HA_C=hsp.ACCT_BASECLS_HA_C
   
WHERE 
--(ha.DISCH_DATE_TIME >= (SELECT EPIC_UTIL.EFN_DIN('mb')))     --discharge date beginning first day of last month
--AND (ha.DISCH_DATE_TIME < (SELECT EPIC_UTIL.EFN_DIN('me')))   --discharge date end last day of last month
--AND ha.COMPLETN_STS_HA_C = 4                                 --CODING complete
--AND
--cancer codes as specified in extract specification
comb.REF_BILL_CODE IN ('2.1','2.2','7.5','7.6','9.3','33.9','C00.1','C00.2','C00.3','C00.4','C00.6','C00.9','C01',
'C02.0','C02.1','C02.2','C02.3','C02.4','C02.9','C03','C03.0','C03.1','C03.9','C03.9','C04','C04.0','C04.1','C04.9',
'C04.a','C07','C08.1','C08.9','C09','C09.0','C09.1','C09.8','C09.9','C10','C10.0','C10.1','C10.2','C10.3','C10.4',
'C10.8','C10.8','C10.9','C11','C11.0','C11.1','C11.2','C11.3','C11.8','C11.9','C12','C13','C13.0','C13.1','C13.2',
'C13.8','C13.9','C14','C14.0','C14.2','C14.8','C15','C15.0','C15.1','C15.2','C15.3','C15.4','C15.5','C15.8','C15.9',
'C16','C16.0','C16.1','C16.2','C16.3','C16.4','C16.5','C16.6','C16.8','C16.9','C17','C17.0','C17.1','C17.2','C17.3',
'C17.8','C17.9','C18','C18.0','C18.1','C18.2','C18.3','C18.4','C18.5','C18.6','C18.7','C18.8','C18.9','C19','C20',
'C21','C21.0','C21.1','C21.2','C21.8','C22','C22.0','C22.1','C22.2','C22.3','C22.4','C22.7','C22.9','C23','C24',
'C24.0','C24.1','C24.8','C24.9','C25','C25.0','C25.1','C25.2','C25.3','C25.4','C25.7','C25.8','C25.9','C26','
C26.0','C26.1','C26.8','C26.9','C30','C30.0','C30.1','C31','C31.0','C31.1','C31.2','C31.3','C31.8','C31.9',
'C32','C32.0','C32.1','C32.2','C32.3','C32.8','C32.9','C33','C34','C34.0','C34.1','C34.2','C34.3','C34.8',
'C34.9','C37','C38','C38.0','C38.1','C38.2','C38.3','C38.4','C38.8','C39','C39.0','C39.8','C39.9','C40','C40.0',
'C40.1','C40.2','C40.3','C40.8','C40.9','C41','C41.0','C41.1','C41.2','C41.3','C41.4','C41.8','C41.9','C43',
'C43.0','C43.1','C43.2','C43.3','C43.4','C43.5','C43.6','C43.7','C43.8','C43.9','C44','C44.0','C44.1','C44.2','C44.3',
'C44.4','C44.5','C44.6','C44.7','C44.8','C44.9','C45','C45.0','C45.1','C45.2','C45.7','C45.9','C46','C46.0','C46.1',
'C46.2','C46.3','C46.7','C46.8','C46.9','C47','C47.0','C47.1','C47.2','C47.3','C47.4','C47.5','C47.6','C47.8','C47.9',
'C48','C48.0','C48.1','C48.2','C48.8','C49','C49.0','C49.1','C49.2','C49.3','C49.4','C49.5','C49.6','C49.8','C49.9',
'C50','C50.0','C50.1','C50.2','C50.3','C50.4','C50.5','C50.6','C50.8','C50.9','C51','C51.0','C51.1','C51.2','C51.8',
'C51.9','C52','C53','C53.0','C53.1','C53.8','C53.9','C54','C54.0','C54.1','C54.2','C54.3','C54.8','C54.9','C55','C56',
'C57','C57.0','C57.1','C57.2','C57.3','C57.4','C57.7','C57.8','C57.9','C58','C60','C60.0','C60.1','C60.2','C60.8',
'C60.9','C61','C62','C62.0','C62.1','C62.9','C63','C63.0','C63.1','C63.2','C63.7','C63.8','C63.9','C64','C65','C66',
'C67','C67.0','C67.1','C67.2','C67.3','C67.4','C67.5','C67.6','C67.7','C67.8','C67.9','C68','C68.0','C68.1','C68.8',
'C68.9','C69','C69.0','C69.1','C69.2','C69.3','C69.4','C69.5','C69.6','C69.8','C69.9','C70','C70.0','C70.1','C70.9',
'C71','C71.0','C71.1','C71.2','C71.3','C71.4','C71.5','C71.6','C71.7','C71.8','C71.9','C72','C72.0','C72.1','C72.2',
'C72.3','C72.4','C72.5','C72.8','C72.9','C73','C74','C74.0','C74.1','C74.9','C75','C75.0','C75.1','C75.2','C75.3',
'C75.4','C75.5','C75.8','C75.9','C76','C76.0','C76.1','C76.2','C76.3','C76.4','C76.5','C76.7','C76.8','C77','C77.0',
'C77.1','C77.2','C77.3','C77.4','C77.5','C77.8','C77.9','C78','C78.0','C78.1','C78.2','C78.3','C78.4','C78.5','C78.6',
'C78.7','C78.8','C79','C79.0','C79.1','C79.2','C79.3','C79.4','C79.5','C79.6','C79.7','C79.8','C80','C81','C81.0',
'C81.1','C81.2','C81.3','C81.7','C81.9','C82','C82.0','C82.1','C82.2','C82.7','C82.9','C83','C83.0','C83.1','C83.2',
'C83.3','C83.4','C83.5','C83.6','C83.7','C83.8','C83.9','C84','C84.0','C84.1','C84.2','C84.3','C84.4','C84.5',
'C85.0','C85.1','C85.7','C85.9','C88.0','C88.1','C88.2','C88.3','C88.7','C88.9','C90','C90.0','C90.1','C90.2',
'C91','C91.0','C91.1','C91.2','C91.3','C91.4','C91.5','C91.7','C91.9','C92','C92.0','C92.1','C92.2','C92.3','C92.4',
'C92.5','C92.7','C92.9','C93','C93.0','C93.1','C93.2','C93.7','C93.9','C94','C94.0','C94.1','C94.2','C94.3','C94.4',
'C94.5','C94.7','C95','C95.0','C95.1','C95.2','C95.7','C95.9','C96','C96.0','C96.1','C96.2','C96.3','C96.7','C96.9',
'C97','CBS','CO2','CO2.a','coa.o','COG','COG.1','COG.2','COG.8','COG.9','COG.O','coo','coo.a','coo.o','COO.S',
'COS.1','COS.2','COS.9','cos.a','COS.O','D00.1','D00.2','D01','D01.0','D01.1','D01.2','D01.3','D01.4','D01.5',
'D01.7','D01.9','D02','D02.0','D02.3','D02.4','D03','D03.0','D03.1','D03.2','D03.3','D03.4','D03.5','D03.6',
'D03.7','D03.8','D03.9','D04','D04.0','D04.1','D04.2','D04.3','D04.4','D04.5','D04.6','D04.7','D04.8','D04.9',
'D05','D05.0','D05.1','D05.7','D05.9','D06','D06.0','D06.1','D06.7','D06.9','D07','D07.0','D07.1','D07.2','D07.3',
'D07.4','D09','D09.0','D09.1','D09.2','D09.7','D09.9','D32','D32.0','D32.1','D32.9','D33','D33.0','D33.1','D33.2',
'D33.3','D33.4','D33.7','D35.2','D35.3','D35.4','D42',
'D42.0','D42.1','D42.9','D43','D43.0','D43.1','D43.2','D43.3','D43.4','D43.7','D43.9','D44.3','D44.4','D44.5','D45',
'D46','D46.0','D46.1','D46.2','D46.3','D46.4','D46.7','D46.9','D47','D47.0','D47.1','D47.2','D47.3','D47.7','D47.9',
'DOO','DOO.O')

Open in new window

Becky Edwards

ASKER

This piece does not work.

,SUBSTRING((@),1,CHARINDEX(',',@)-1) AS PatientFirst

The error is:
Invalid length parameter passed to the LEFT or SUBSTRING function.

Also, is it possible to build this as a separate subquery in the main query, so that I can have the following code included:

from emergency_contacts ec
INNER JOIN ZC_EMERG_PAT_REL ZC ON ec.EMERG_PAT_REL_C = zc.EMERG_PAT_REL_C
where zc.EMERG_PAT_REL_C = 17)

?

Thank you.

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

Hi,
Is the query given above worked for the first part?

Regards,
Pawan

Becky Edwards

ASKER

Well I thought it was. I was just informed that in order for SSIS to keep the correct amount of characters (or spaces) in a field, we need to use the CAST function.

SELECT
CAST('' as char(5)) AS Hospital_ID --no hospital ids have 5 or less characters
,CAST((p.PAT_LAST_NAME) as char(20)) AS PatientLast

This way the extract displays the correct width for each field.

See attached example.

Now I have to rebuild each of these using CAST.

I still need to split the one field into two, regardless.

Becky
metriq_formatting.docx

Pawan Kumar

Excellent logic Becky !!

Regards,
Pawan