Replace Function not replacing all the records of the table

[arsheena.alam]

Hi,

I have a table with more than 3000 records. I need to replace a particular string of all the rows wid some other string But when i am using replace function Its only replacing half of the rows of the table while the other half are not getting replaced.
I m using the following query:

UPDATE    Table
SET Source = REPLACE(Source, 'src="~/','src ="http://www.mywebsite.co.in/')
WHERE     (Source LIKE '%src="~/%')

Please let me know the solution to this!!

Thanks in advance!!

[adam_k]

When you select using the WHERE in your query, do you get all the results or just the ones replaced?

[arsheena.alam]

While running the query i get the following message..

1127 ROWS AFFECTED.

moreover There are 3000 records in the table and when i check the database after running this query only 300 records have been replaced rest are still same....

[adam_k]

You are using a where, which means that you are filtering the records.
If you run a select with the same where, I'm pretty sure that you'll get 1127 records, so I don't see a problem with the result 1127 ROWS AFFECTED.

As for the 300 records only getting updated, I don't have an explanation. Can you tell us what SQL Server you are working on and the data type of Source?

[arsheena.alam]

I am using SQL SERVER 2005. And moreover i would like to highlight that the string that is to be replaced is contained in every row so that means even if i use where clause it should affect all the 3000 records in the table

[adam_k]

Verify that your where returns true for all 3000 records by using it with a select.
If that also returns 1127 records and you don't find any reasons for this, check you don't have a SET ROWCOUNT in place (if you are not running the query directly from Management Studio).

PS: Check that there are no triggers messing up with your update.

[BitBlt]

...I m using the following query:

UPDATE    Table
SET Source = REPLACE(Source, 'src="~/','src ="http://www.mywebsite.co.in/')
WHERE     (Source LIKE '%src="~/%')

Are you really using this exact query on SQL2005? When I try to run your code I get a syntax error because "Table" is a reserved word. Are you using "update [Table]" or something like that?

Also, I built a little table and populated it with some test data, and it worked fine. What's the table structure of [Table]? What are the datatypes? Unicode (nchar or nvarchar)?

[arsheena.alam]

Hi @adam I am running this query directly from management studio. Its seriusly strange that if I am trying to update a single record through where clause than also the record is not getting replaced. IS there any error in the replace function I am using????????

@BitBlt the word "Table" is just used for reference. the correct table name is "Quest_Bank" and the column name that is to be updated is "Source" which is varchar type.
so the correct query is:

UPDATE   Quest_Bank
SET Source = REPLACE(Source, 'src="~/','src ="http://www.mywebsite.co.in/')
WHERE     (Source LIKE '%src="~/%')

[urtrivedi]

are you getting expected number of records by selecting it
say

select count(*) from quest_bank where source like '%src="~/%'

I think you might be having space in between in some records [ src = "~/ ] like that

[arsheena.alam]

@urtrivedi Yes I am getting the expected records after running SELECT count(*) FROM quest_bank WHERE source LIKE '%src="~/%'.

ANd I have checked there are no spaces in between

[urtrivedi]

after keeping backup, i suggest you to run without where condition.

[arsheena.alam]

I did that too and as a result the columns of all the rows got updated with just "src ="http://www.mywebsite.co.in/".

[arsheena.alam]

Now again after restoring the backup i tried running the same query it replaced all the records.. Hence my problem solved.

Thanks all :)