locked
How to replace characters RRS feed

  • Question

  • User194385433 posted

    Hi,

    My Table out put shows like below:
    
    Q1              Q2
    12:1           13:1
    0:1            10:7
    1:1            60:1
    
    SELECT REPLACE(Q1,'0 :1','0 :NA') as Q1,REPLACE(Q2,'0 :1','0 :NA') as Q1 From  #tmp
    
    By using above Sql Statement I am getting below output(The Out Put is Wrong)
    
    Q1      Q2
    12:1   13:1
    0: NA  1 :NA
    1:1    6 :NA
    
    
    Here i need to replace only if 0 :1 contains:
    
    Actual Out Put I need Like:
    
    Q1       Q2
    12:1    13:1
    0:NA    10:7
    1:1     60:1

    Thanks,

    Tuesday, December 15, 2015 11:32 PM

Answers

  • User-1716253493 posted

    Try this

    SELECT CASE Q1 WHEN '0:1' THEN '0:NA' ELSE Q1 END as Q1

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 16, 2015 2:15 AM

All replies

  • User-1716253493 posted

    Try this

    SELECT CASE Q1 WHEN '0:1' THEN '0:NA' ELSE Q1 END as Q1

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 16, 2015 2:15 AM
  • User77042963 posted
    Create table #tmp (Q1 varchar(30),Q2 varchar(30))
    Insert into #tmp values('12:1','13:1'),
    ('0:1','10:7'),
    ('1:1','60:1') 
    
    
    select  
    Case when patindex('0:1',Q1)>0 Then  REPLACE(Q1,'0:1','0:NA') else Q1 End Q1,
    Case when patindex('0:1',Q2)>0 Then  REPLACE(Q2,'0:1','0:NA') else Q2 End Q2
    
    
    from #tmp
    
    drop table #tmp

    Wednesday, December 16, 2015 3:37 PM
  • User-62323503 posted

    Case when patindex('0:1',Q1)>0 Then REPLACE(Q1,'0:1','0:NA') else Q1 End Q1,

    Dear limno,

    I think, there is no need to check the value to be replaced with patindex as if value exists replace function will replace the value otherwise it would return value of Q1

    select  REPLACE(Q1,'0:1','0:NA') Q1, REPLACE(Q2,'0:1','0:NA') Q2 from #tmp

    This would also give the same result.

    Suresh,

    I think only issue is that you have the spaces in the value in the replace function.

    Thursday, December 17, 2015 5:48 AM
  • User77042963 posted

    Sandeep,

    The use of patindex function (find exact 0:1 format)  is to skip the data in the form presented in Q2 column:

    60:1

    You can try it out and let me know if you still think there is an issue.

    Thanks.

    Thursday, December 17, 2015 9:25 PM