Multiple Replace Using Replace() Function
-
I am doing the following in my stored procedure:
select replace(replace(replace(replace(t.comments,'''','''apos;'),'&','&'),'<','<'),'>','>') as "Comm" from table_name
Is there another or better way to perform a multiple replace on a value returned from a query? Is my code considered efficient? Would it be better to do this priot to performing the select? Any feedback or suggestions would be greatly appreciated.
-
If you want to keep the original data, then you won't have more efficient than this. You can't multiple replaces at one time in sql server so you have to include them within one another.
If you don't want to keep the original data, then you can use a trigger to replace the data on insert/update so that you perform the operation only once.
If you don't want to change it, I would suggest you create a computed column with that expression, or better make a function a refer to the function in the computed column so you can reuse the code. That would give a prettier select statement
. -
Thanks again Remi!
-
A scalar UDF might make the SELECT a bit more cleaner and easier to read, but will kill performance as it works row-by-row, compared to REPLACE which works on sets. Any chance to change to underlying data?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
It wasn't too long ago that I read here that there was a way to make the functions work pretty fast (even if it's row by row). It had something to do with begin a deterministic function that was simply manipulating parameters and not, absolutely not, accessing any tables in the process. If I remember correctly it was a wee bit slower but not by a considerable marging... but we'd have to test this theory and I don't have the time.
-
If you have to do a lot of funky replacing and such, I HIGHLY recommend using regular expressions, particularly the PCRE extended ones available at this link.
http://www.codeproject.com/database/xp_pcre.asp
Seems a lot of SQL people jump through a lot of hoop to avoid regular expressions but it's really worth learning and can save you a LOT of work when you have to do complex replacing and/or splitting of strings.
For example, using xp_prce_replac(), you could take these phone numbers:
1-999/555-1212
1.999.555.1212
(999)555-1212
999/555-1212
999-555-1212
and easily make them all look exactly the same:
(999) 555-1212 for example.
Check it out.
G. Milner
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply