Update and replace

Transact-SQL
1

Hallo SQL Cracks.

Strugling...Strugling...

update ARTIKEL set BESCHREIBUNG = replace(BESCHREIBUNG, 'aktion_discount_rabatt.gif', 'new string');

I want to replace in a product describtion a text

Artikel
Beschreibung (inside here i want to replace)

I am using mssql 10....

thanks Rene code_dummy

2

your query will update all rows in table ARTIKEL. Is that what you want? if not, you might want to add a WHERE clause.

3

update ARTIKEL
set BESCHREIBUNG = replace(BESCHREIBUNG, 'aktion_discount_rabatt.gif', 'new string')
where BESCHREIBUNG like '%aktion_discount_rabatt.gif%'

4

Get e Error somthing like NTEXT ----

5

cast to nvarchar before replacing, and cast back to ntext when done

6

Ugh (nicht gut!). Convert the ntext to nvarchar(max) as soon as you reasonably can.

update ARTIKEL
set BESCHREIBUNG = replace(cast(BESCHREIBUNG as nvarchar(max)), 'aktion_discount_rabatt.gif', 'new string')
where cast(BESCHREIBUNG as nvarchar(max)) like '%aktion_discount_rabatt.gif%'

7

I'd change the DDL of the database !!

1 Like
8

Yes even better to do that (if possible).

9

You have to check the code first. Obviously any READTEXT, TEXTPTR, etc. would give errors once the column is no longer text.