locked
Query help RRS feed

  • Question

  • Hi experts

    create table tb_ip_address
    (
    id int,
    ip_addr varchar(100)
    );

    insert into tb_ip_address values(1,'tcp://10.44.92.48:5060');
    insert into tb_ip_address values(2,'tcp://10.44.88.72:1443');
    insert into tb_ip_address values(3,'tcp://10.44.92.48:6161');

    Replace with 

    10.44.92.48  to 10.44.71.25

    and 5060         to 1443

    if port is 6161 then leave as it is

    Expected result

    ID       IP_ADDR

    1  tcp://10.44.71.25:1443
    2  tcp://10.44.88.72:1443
    3  tcp://10.44.71.25:6161

    Monday, October 29, 2018 1:25 PM

Answers

  • UPDATE tb_ip_address
    SET ip_addr = REPLACE(REPLACE(ip_addr,'10.44.92.48','10.44.71.25'),':5060',':1443')
    WHERE ip_addr LIKE '%10.44.92.48:5060%'


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Ramesh Babu Vavilla Monday, October 29, 2018 1:39 PM
    • Marked as answer by Antonioy Tuesday, October 30, 2018 5:18 AM
    Monday, October 29, 2018 1:30 PM

All replies

  • UPDATE tb_ip_address
    SET ip_addr = REPLACE(REPLACE(ip_addr,'10.44.92.48','10.44.71.25'),':5060',':1443')
    WHERE ip_addr LIKE '%10.44.92.48:5060%'


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Ramesh Babu Vavilla Monday, October 29, 2018 1:39 PM
    • Marked as answer by Antonioy Tuesday, October 30, 2018 5:18 AM
    Monday, October 29, 2018 1:30 PM
  • Try this:
    use tempdb
    GO
    create table tb_ip_address
    (
    id int,
    ip_addr varchar(100)
    );
    
    insert into tb_ip_address values(1,'tcp://10.44.92.48:5060');
    insert into tb_ip_address values(2,'tcp://10.44.88.72:1443');
    insert into tb_ip_address values(3,'tcp://10.44.92.48:6161');
    GO
    update tb_ip_address  set ip_addr =replace(replace(ip_addr, '10.44.92.48','10.44.71.25'), '5060','1443')
    GO
    select *  From tb_ip_address  
    

    Monday, October 29, 2018 1:30 PM
  • Hi Antonioy,

    Per your description, you would like to replace a string to another string. Right? 

     

    Did you mean that you would like to update the original table ? If so, you can follow above advice. If not , you can use SELECT to achieve your requirement .You can use REPLACE() to replace directly.

     
    create table tb_ip_address
    (
    id int,
    ip_addr varchar(100)
    );
    
    insert into tb_ip_address values(1,'tcp://10.44.92.48:5060');
    insert into tb_ip_address values(2,'tcp://10.44.88.72:1443');
    insert into tb_ip_address values(3,'tcp://10.44.92.48:6161');
    
     select id,replace(replace(IP_ADDR,'10.44.92.48','10.44.71.25'),'5060','1433') as ip_addr
     from tb_ip_address
     /*
     id          ip_addr
    ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1           tcp://10.44.71.25:1433
    2           tcp://10.44.88.72:1443
    3           tcp://10.44.71.25:6161
     */

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by 1-----------1 Tuesday, October 30, 2018 4:38 AM
    Tuesday, October 30, 2018 2:07 AM