Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Posts
    18

    Unanswered: help on replace command

    Hi All,

    I have a problem with replace command.

    I have data in column like 'www.abc.com/inbox/../../' and I need to replace it with 'www.xyzw.com/box/inbox/../../' , have total 1500 rows starting data with 'www.abc.com/inbox'. Now I need to update all the 1500 rows with 'www.xyzw.com/box/inbox' and remaining part of URL should be same with an update statement.

    UPDATE tab1
    SET col1 = REPLACE(col1, SUBSTRING(col1, charindex('www.abc.com/inbox',col1),17), 'www.xyzw.com/box/inbox') where col1 like '%abc%';

    but I am getting error like data is being truncated. I am using SQL SERVER 2000.

    Please help me in this regard.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What is the data type of col1?

    When you replace "www. shortURL.com/inbox" by "www. extraveryverylongURL.com/box/inbox", the resulting string could exceed the length N of the VARCHAR(N), CHAR(N), ... data type.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Oct 2010
    Posts
    18
    Hi Wim,

    Thanks for the reply. but col1 has varchar(255) datatype and the URL has 140 characters. but if I update the record individually like put col1=condition its working perfectly fine. When I put like operator in where condition, facing this error.

    Please help me.
    Last edited by ashwinjoshi; 03-08-12 at 23:10.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What does this return?
    Code:
    select max(LEN(col1)) from MyTable
    140 ?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Oct 2010
    Posts
    18
    My bad... It was my mistake. as it was exceeding characters. now got resolved.

    thanks for your maxlength query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •