create view ENTRP_SERVS.V_IRS_BULK as
Select
Atr.Ssn_Tx
,Names.First_Na
,Names.Middle_Na
,Names.Last_Na
,Reff.Ref_Cd_Desc_Tx Suffix_Na
,MBR.PRSN_MBRSH_ID,
T_PRSN_MBRSH_TAX.FILTER_TYPE_CD
From Hix.T_Enrt En Inner Join Hix.T_Prsn_Enrt Pe On En.Enrt_Id = Pe.Enrt_Id And En.Enrt_Status_Cd In (428,439, 430 ) And Pe.Active_In = 'Y' And (Pe.Retro_In Is Null Or Pe.Retro_In = 'N')
Left Outer Join Hix.T_Aptc Ap On En.Enrt_Id = Ap.Enrt_Id
Left Outer Join Hix.T_Prsn_Addl_Attr Atr On Pe.Prsn_Mbrsh_Id = Atr.Prsn_Mbrsh_Id
Left Outer Join
(
Select nAc.Prsn_mbrsh_Id, First_Na , Middle_Na , Last_Na, Suffix_Cd From Hix.T_Prsn_Name Na, Hix.t_prsn_mbrsh_prsn_name_ac nAc
where na.prsn_name_id = nAc.prsn_Name_Id
) Names On Pe.prsn_mbrsh_id = Names.prsn_mbrsh_id
Left Outer Join Hix.T_Ref_Data Reff On Names.Suffix_Cd = Reff.Ref_Data_Id
Left Outer Join Hix.T_Ref_Data Re On Atr.GENDER_CD = Reff.Ref_Data_Id
Left Outer Join Hix.T_PRSN_MBRSH MBR on MBR.PRSN_MBRSH_ID = Pe.PRSN_MBRSH_ID
Left Outer Join
(SELECT (CASE WHEN FILLING_STATUS_CD IN (2061,2062,2063) OR (FILLING_STATUS_CD=2063 AND PRIM_TX_FILER_IN ='Y') THEN 'PRIMARY'
WHEN FILLING_STATUS_CD=2063 AND PRIM_TX_FILER_IN ='N' THEN 'SPOUSE'
WHEN FILLING_STATUS_CD IN (2066, 2068) THEN 'DEPENDENT'
ELSE NULL
END) AS FILTER_TYPE_CD,PRSN_MBRSH_ID FROM HIX.T_PRSN_MBRSH_TAX WHERE TAX_FL_YR_NB=YEAR(CURRENT DATE)-2)T_PRSN_MBRSH_TAX
ON PE.PRSN_MBRSH_ID=T_PRSN_MBRSH_TAX.PRSN_MBRSH_ID WHERE T_PRSN_MBRSH_TAX.FILTER_TYPE_CD IS NOT NULL
I have this view and I want to remove the apostrophe (') from the FIRST_NA, MIDDLE_NA and LAST_NA columns. How can I do that?
Best Answer
Use the
REPLACE
function, e.g.Here we are replacing a single quote character with an empty string. Since SQL character literals are themselves enclosed in single quotes, we escape the single quote character in the literal by doubling it.
More information about the use of the
REPLACE
function can be found in the manual, as is often the case.