replace function in SQL?

Hi,

I have a table with a field called productname, and it has about 5000 rows, and within that about 1000 have a productname that has 'NIB' in the name, ie "My Product NIB DVD" and I have been asked to replace 'NIB' with 'New' ie "My Product New DVD" Can I do this in SQL using an Update statement? Or do I have build something in maybe asp.net to use a replace function to change the name.

ThanksThere is a replace function. Look in your transact SQL reference and it will give you the nitty gritty. Maybe something like
Update mytable set mycolumn = replace(mycolumn, 'NIB', 'New')|||I guess I just should have looked a little harder, right after I posted that message, I found out its just:

UPDATE Products
SET ProductName = REPLACE(ProductName, 'NIB', 'NEW')

For some reason, the SQL book that I have doesn't have anything about REPLACE()

thanks anyways|||I'm using Microsoft SQL Server Management Studio and I tried this statement:

UPDATE alumni.enewsLetter
SET sum_nws = REPLACE(sum_nws, '\r\n\', '');

Error:
Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of replace function.

Then I tried this:
SELECT REPLACE(alumni.enewsLetter.sum_nws, '\r\n\', '');

Error:
Msg 107, Level 16, State 2, Line 1
The column prefix 'alumni.enewsLetter' does not match with a table name or alias name used in the query.

Help is appreciaeted.|||

Doesn't work on text fields.

BTW, here is the link to the function description:http://msdn2.microsoft.com/en-us/library/ms186862(d=ide).aspx

You could try changing the field datatype from text to varchar(max), perform your update which I would recommend you change to:

UPDATE field=REPLACE(field,'\r\n\','') FROM table WHERE field LIKE '%\r\n%'

That will tell SQL Server to only update the fields that actually contain \r\n instead of updating all fields.

Then change the datatype back to text again, just incase you have code that relies on the fact that it is a text field.

|||

Motley:

You could try changing the field datatype from text to varchar(max), perform your update which I would recommend you change to:

UPDATE field=REPLACE(field,'\r\n\','') FROM table WHERE field LIKE '%\r\n%'

That query statement gave me an error so I tried this:
UPDATE alumni.enewsLetter
SET sum_nws = REPLACE(sum_nws, '\r\n\', ' ')
WHERE sum_nws LIKE '%\r\n%';

And it says "3 rows affected by last query" but nothing happens. Allthe \r\n are still there. And yes, I did change the data type tovarchar.|||

Try:

SELECT sum_nws
FROM alumni.enewsLetter
WHERE sum_nws LIKE '%\r\n%'

And see what 3 rows it returns. Then I would see if I could figure out why either the LIKE isn't returning the other rows (if any) that contain \r\n, or if there are only 3 rows, then why the replace isn't working properly. Sorry I can't be of more help.

|||That select statement does return the three rows that contains the \r\nin it. The problem is why is the update not replacing those characters.Any other idea?

Again, thanks so much!|||

Then try:

SELECT sum_nws,REPLACE(sum_nws,'\r\n',''),REPLACE(sum_nwsm'\\r\\n','')
FROM alumni.enewsLetter
WHERE sum_nws LIKE '%\r\n%'

See which replace works, I'm too lazy to look up and see if you need to escape \ with a \ or not.

|||I recieved Line 1: Incorrect syntax enar '\\n\\n'.
I tried having just one back slash but still giving me the incorrect syntax error near '\r\n'