Question is pretty self explanitory. I want to do a simple find and replace, like you would in a text editor on the data in a column of my database (which is MsSQL on MS Windows server 2003)
+6
A:
example, this will replace all the a characters with b
UPDATE YourTable
SET Column1 = REPLACE(Column1,'a','b')
WHERE Column1 LIKE '%a%'
Btw there is no SQL server 2003
SQLMenace
2008-09-12 13:59:36
oops! it's Server 2003
Jiaaro
2008-09-12 14:01:07
+1
A:
like so:
BEGIN TRANSACTION; UPDATE table_name
SET column_name=REPLACE(column_name,'text_to_find','replace_with_this'); COMMIT TRANSACTION;
Example: Replaces <script... with <a ... to eliminate javascript vulnerabilities
BEGIN TRANSACTION; UPDATE testdb
SET title=REPLACE(title,'script','a'); COMMIT TRANSACTION;
Jiaaro
2008-09-12 13:59:51
If you're actually planning on using that in production, enjoy your unintended side effects of sweeping contextless string substitutions.
Will
2008-09-12 14:05:21
no it was a 'run this one time to fix an sql injection attack' type of thing... now I have to convince the powers that be that we need server-side authentication. Javascript authentication is NOT authentication haha
Jiaaro
2008-09-14 18:15:38
A:
If you are working with SQL Server 2005 or later there is also a CLR library available at http://www.sqlsharp.com/ that provides .NET implementations of string and RegEx functions which, depending on your volume and type of data may be easier to use and in some cases the .NET string manipulation functions can be more efficient than T-SQL ones.
Joe Kuemerle
2008-09-12 16:42:45