1
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?

1

Use the REPLACE function, e.g.

select replace(yourfield,'''','') from yourtable where ... 

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.

  • I just edited the question. Can you please answer it now? Thanks :) – user38759 Jul 8 '14 at 16:02
  • What seems unclear in my answer? – mustaccio Jul 8 '14 at 16:32
  • The two fields after NAME in the Resplace function and also the "values" keyword. And its specific to one valueONEIL. But i am retrieving more than 1000 records in my view. – user38759 Jul 8 '14 at 16:38
0

what about you do something using the replace function alongside the char(39) character just like in the example below:

declare @the_string nvarchar(100) = 'His Name is James O''Brian'
select @the_string
select REPLACE(@the_string, char(39), '')

this will give you the following result:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.