Total mental block here as not needed to do this for a while, whats the command to replace text in a variable on the way out (ie the equivalent of a javascript VAR.replace() type command)

Ie

Select Name.replace('Chip', 'Cunt') from Users

Its NOT that, just hopefully that should make sense as to what im trying to do!

 

replace(a.name, 'chip', 'cunt')

 

Ah, right function name, wrong syntax.

Many thanks

 

UPDATE Users SET Name = 'Cunt' WHERE Name = 'Chip'

.. slightly more orthadox?

 

No, because that will change the data in the table, I dont want to touch the actual data, I just want to manipulate a copy of it.

What im actually doing is crossreferencing from one table to another, and the data in the first table can be tagged with thing like <asset>some-item-name</asset> to say that when rendering a page from that table you need to go and insert an asset with that item name, and some software im writing needs to do a similar thing, but I cant just do where "this = that" I have to do where "this = replace(replace(that,'<asset>',''),'<asset>','')

So that: <asset>some-item-name</asset>
becomes: some-item-name
during the query, but the underlying data isnt altered.


So my actual query ended up like this:

select bv_editorial.author, main_graphic, ad_abstract, location from bv_editorial, bv_advertisement, tgb_assets where tgb_assets.name = replace(replace(ad_abstract,'</asset>',''),'<asset>','') and ad_name=main_graphic and bv_editorial.store_id = 101 and bv_editorial.status =1 and bv_editorial.deleted=0 and main_graphic is not null and ad_abstract like '<asset>%'

 

Fair enough, I didn't read the original question properly. Personally i'm a fan of doing this in the code itself* rather in the query, but I doubt either of us can be arsed to do a profiling exercise to display which method is better

* Unless this is in stored proc

 

doing it in the code itself, in the environment i work in normally, would mean moving all the data into memory on the machine where the code executes, rather than manipulating it on the DB server where it already is, so for that reason id use a stored procedure

Anyway, in this particular instance its just for a report, so only needs to be run directly in SQL anyway, so without a doubt is the best way of doing so.

 

No probs

 

As chip said, that would update the table directly, however, what you're suggesting isn't the same as:

update users set name = replace(name,'Cunt','Chip') which would update all occurrences of Cunt in the name column regardless of its contents (rows where name is 'My name is Cunt', 'I am a Cunt', etc...)

TRANSLATE is also a very useful function if you want to replace single characters with others

 

Frog, correct, in this instance I was only after replacing a small part of the text (removing in fact) not doing an update of only rows with EXACTLY that as the whole entry.

 

Regardless of profiling and performance, this raises a whole host of questions in terms of code stability and maintainability where database structures are inherently stored in non DB code (e.g. linked queries, db updates in application server or web server), as opposed to abstracted by means of stored procedures, functions, packages and views

 

Yes, also true