SQL help - SORTED - Thanks Shonky
#1
SQL help - SORTED - Thanks Shonky
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!
Ie
Select Name.replace('Chip', 'Cunt') from Users
Its NOT that, just hopefully that should make sense as to what im trying to do!
Last edited by Chip; 14-05-2008 at 01:52 PM.
#2
V6 Ka of Love
Join Date: Jun 2004
Location: South Wales
Posts: 460
Likes: 0
Received 0 Likes
on
0 Posts
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!
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')
#5
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>%'
Last edited by Chip; 14-05-2008 at 02:09 PM.
#6
Colossal Pervert
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
* Unless this is in stored proc
#7
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.
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.
Trending Topics
#9
0-60 in 17 seconds (eek)
Join Date: Apr 2004
Location: Berkshire
Posts: 6,717
Likes: 0
Received 0 Likes
on
0 Posts
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
#11
0-60 in 17 seconds (eek)
Join Date: Apr 2004
Location: Berkshire
Posts: 6,717
Likes: 0
Received 0 Likes
on
0 Posts
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
Last edited by frog; 14-05-2008 at 04:12 PM.
Thread
Thread Starter
Forum
Replies
Last Post
wheelwizardrefurbs
Technical help Q & A
5
22-09-2015 05:45 PM