Wednesday, October 24, 2012

Replace partial text string in SQL Text field

Even though this is very simple, I thought of blogging this.

I had a table called tbl_Item which had a Field called URL(nvarchar(200)) having several records like 'http://192.168.0.20:8080/Items/T-shirt.jpg', 'http://192.168.0.20:8080/Items/Jean.jpg'.
I wanted to replace the part of the string which contains '192.168.0.20:8080' to '10.20.10.71'. Here it goes.

UPDATE tbl_Item
SET Url = replace(cast(Url as NVARCHAR(200)),'192.168.0.20:8080','10.20.10.71')