t-sql replace on text field

sql-server sql-server-2000
PeteT picture PeteT · Nov 14, 2008 · Viewed 54.4k times · Source

I have hit a classic problem of needing to do a string replace on a text field in an sql 2000 database. This could either be an update over a whole column or a single field I'm not fussy.

I have found a few examples of how to use updatetext to achieve it but they tend to be in stored procedures, does anyone know of a similar thing that is wrapped into a function so I can use it like I would usually use Replace(). The problem with the Replace() function for anyone who isn't aware is that it doesn't support text fields.

Edit: I realised I could probably get away with varchar(8000) so have swapped the fields to this type which fixes the issue. I never found a true solution.

Answer

suryakiran picture suryakiran · Oct 26, 2009

Here is the sample query to update table with text column using REPLACE function. Hope this is useful for you.

UPDATE <Table> set textcolumn=
REPLACE(SUBSTRING(textcolumn,1,DATALENGTH(textcolumn)),'findtext','replacetext') 
WHERE <Condition>

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Check if table exists in SQL Server

I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements. When you Google for the answer, you get so many different answers. Is there an official/backward …

Read More
How to Join to first row

I'll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098FBE3...} 1 7 prefabulated amulite {1609B09...} 2 32 spurving bearing But occasionally there …

Read More