none
Search digits in string and replace RRS feed

  • Question

  • Hi, I want to update the Codetype column. I want to search the two digit in LAST THREE characters and replace with @codetype. If any value has no character at the end then search the two digit in LAST TWO characters and replace with @codetype.

    Example: If I search last three character in Codetype to find the digit then I find '04'. Now I want to replace with @codetype which is '06'. One value '012304' has no character at the end then I will search last two character and find the digit and replace.

    create table #Code (code char(8), Codetype char(18))
    insert into #Code values ('1200','012304')
    insert into #Code values ('1200','012304A')
    insert into #Code values ('1200','012304B')
    insert into #Code values ('1200','042304C')
    insert into #Code values ('1200','042303D')

    Declare @codetype varchar(18)='06'
    --Update #code set Codetype=

    Expected Result

    Select * from #code
    code  Codetype
    1200     012306           
    1200     012306A          
    1200     012306B          
    1200     042306B          



    • Edited by Khan_K Wednesday, August 24, 2016 11:56 AM
    Wednesday, August 24, 2016 11:52 AM

Answers

  • create table #Code (code char(8), Codetype varchar(18))
    
    --Change to varchar(18)
    
    insert into #Code values ('1200','012304')
    insert into #Code values ('1200','012304A')
    insert into #Code values ('1200','012304B')
    insert into #Code values ('1200','042304C')
    insert into #Code values ('1200','042303D')
    
    Declare @codetype varchar(18)='06'
    --Update #code set Codetype=
    
    
    
    update #Code
    set Codetype=Left(Codetype,len(Codetype)-3)+replace(right (Codetype,3),'04',@codetype)
    where right (Codetype,3) like '%04%'
    
    select * from #Code
    
    
    drop table #Code

    • Marked as answer by Khan_K Wednesday, August 24, 2016 6:45 PM
    Wednesday, August 24, 2016 12:47 PM
    Moderator
  • Need to add an LTRIM(RTRIM(codetype))

    update #code set  Codetype = case when RIGHT(RTRIM(LTRIM(codetype)), 1) like '[0-9]' 
    							 Then STUFF(codetype, len(codetype) - 1, 2, @Replace)
    						    ELSE
    						       STUFF(codetype, len(codetype) - 2, 2, @Replace) END

    • Marked as answer by Khan_K Wednesday, August 24, 2016 6:45 PM
    Wednesday, August 24, 2016 1:29 PM
  • Hi, The Answer is not correct. This update script is replacing '04' with '06' from everywhere but please see my condition for the update. Thanks,

    Did you check the code I posted:

    create table #Code (code char(8), Codetype varchar(18))
    
    --Change to varchar(18)
    
    insert into #Code values ('1200','012304')
    insert into #Code values ('1200','012304A')
    insert into #Code values ('1200','012304B')
    insert into #Code values ('1200','042304C')
    insert into #Code values ('1200','042303D')
    
    Declare @codetype varchar(18)='06'
    --Update #code set Codetype=
    
    
    
    update #Code
    set Codetype=Left(Codetype,len(Codetype)-3)+replace(right (Codetype,3),'04',@codetype)
    where right (Codetype,3) like '%04%'
    
    select * from #Code
    
    
    drop table #Code

    • Marked as answer by Khan_K Wednesday, August 24, 2016 6:45 PM
    Wednesday, August 24, 2016 1:40 PM
    Moderator
  • Another way:

    create table #Code (code char(8), Codetype varchar(18))
    
    --Change to varchar(18)
    
    insert into #Code values ('1200','012304')
    insert into #Code values ('1200','012304A')
    insert into #Code values ('1200','012304B')
    insert into #Code values ('1200','042304C')
    insert into #Code values ('1200','042303D')
    
    Declare @codetype varchar(18)='06'
    --Update #code set Codetype=
    
    
    
     
    Update #Code
    Set Codetype= Reverse(Stuff(Reverse(Codetype), patindex('%40%',Left(Reverse(Codetype),3)),2,'60'))  
    Where patindex('%40%',Left(Reverse(Codetype),3))>0
    
    select *  from #Code
    
    
    drop table #Code

    • Marked as answer by Khan_K Wednesday, August 24, 2016 6:46 PM
    Wednesday, August 24, 2016 2:34 PM
    Moderator

All replies

  •  

     

     

    update#code

    set Codetype = replace(codetype,'04',@codetype)



    • Edited by Enric Vives Wednesday, August 24, 2016 11:59 AM
    Wednesday, August 24, 2016 11:57 AM
  • create table #Code (code char(8), Codetype varchar(18))
    
    --Change to varchar(18)
    
    insert into #Code values ('1200','012304')
    insert into #Code values ('1200','012304A')
    insert into #Code values ('1200','012304B')
    insert into #Code values ('1200','042304C')
    insert into #Code values ('1200','042303D')
    
    Declare @codetype varchar(18)='06'
    --Update #code set Codetype=
    
    
    
    update #Code
    set Codetype=Left(Codetype,len(Codetype)-3)+replace(right (Codetype,3),'04',@codetype)
    where right (Codetype,3) like '%04%'
    
    select * from #Code
    
    
    drop table #Code

    • Marked as answer by Khan_K Wednesday, August 24, 2016 6:45 PM
    Wednesday, August 24, 2016 12:47 PM
    Moderator
  • Hi, The Answer is not correct. This update script is replacing '04' with '06' from everywhere but please see my condition for the update. Thanks,
    Wednesday, August 24, 2016 12:54 PM
  • Try this

    create table #Code (code char(8), Codetype char(18))
     insert into #Code values ('1200','012304')
     insert into #Code values ('1200','012304A')
     insert into #Code values ('1200','012304B')
     insert into #Code values ('1200','042304C')
     insert into #Code values ('1200','042303D')
     
    declare @Replace varchar(2) = '99'
    
    update #code set  Codetype = case when RIGHT(codetype, 1) like '[0-9]' 
    							  Then STUFF(codetype, len(codetype) - 1, 2, @Replace)
    						    ELSE	   
    							       STUFF(codetype, len(codetype) - 2, 2, @Replace) END
    							  
    	
    select codetype from #code

    Wednesday, August 24, 2016 1:04 PM
  • Need to add an LTRIM(RTRIM(codetype))

    update #code set  Codetype = case when RIGHT(RTRIM(LTRIM(codetype)), 1) like '[0-9]' 
    							 Then STUFF(codetype, len(codetype) - 1, 2, @Replace)
    						    ELSE
    						       STUFF(codetype, len(codetype) - 2, 2, @Replace) END

    • Marked as answer by Khan_K Wednesday, August 24, 2016 6:45 PM
    Wednesday, August 24, 2016 1:29 PM
  • Hi, The Answer is not correct. This update script is replacing '04' with '06' from everywhere but please see my condition for the update. Thanks,

    Did you check the code I posted:

    create table #Code (code char(8), Codetype varchar(18))
    
    --Change to varchar(18)
    
    insert into #Code values ('1200','012304')
    insert into #Code values ('1200','012304A')
    insert into #Code values ('1200','012304B')
    insert into #Code values ('1200','042304C')
    insert into #Code values ('1200','042303D')
    
    Declare @codetype varchar(18)='06'
    --Update #code set Codetype=
    
    
    
    update #Code
    set Codetype=Left(Codetype,len(Codetype)-3)+replace(right (Codetype,3),'04',@codetype)
    where right (Codetype,3) like '%04%'
    
    select * from #Code
    
    
    drop table #Code

    • Marked as answer by Khan_K Wednesday, August 24, 2016 6:45 PM
    Wednesday, August 24, 2016 1:40 PM
    Moderator
  • Another way:

    create table #Code (code char(8), Codetype varchar(18))
    
    --Change to varchar(18)
    
    insert into #Code values ('1200','012304')
    insert into #Code values ('1200','012304A')
    insert into #Code values ('1200','012304B')
    insert into #Code values ('1200','042304C')
    insert into #Code values ('1200','042303D')
    
    Declare @codetype varchar(18)='06'
    --Update #code set Codetype=
    
    
    
     
    Update #Code
    Set Codetype= Reverse(Stuff(Reverse(Codetype), patindex('%40%',Left(Reverse(Codetype),3)),2,'60'))  
    Where patindex('%40%',Left(Reverse(Codetype),3))>0
    
    select *  from #Code
    
    
    drop table #Code

    • Marked as answer by Khan_K Wednesday, August 24, 2016 6:46 PM
    Wednesday, August 24, 2016 2:34 PM
    Moderator