Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 26-05-2009
Member
 
Join Date: May 2008
Posts: 372
SQL find and replace query

Hello

I have a small problem with my SQL database. I still have parts from old url's in my forum posts. Instead of good 3000 post by hand to change a little, I could write SQL command, which to me all the old url's are replaced by new.

I've no idea how I should write this command ...

For eg:

REPLACE INTO `forum_posts` SET `post` = 'up-space.theplayers.ch'
WHERE `post` = 'fini.techarena.in / upspace'

This is the actual spirit quite close but not exact.

I want from the table in the column forum_posts post all entries "fini.techarena.in/upspace" by "up-space.theplayers.ch replace.

Can anyone of you help me?
Reply With Quote
  #2  
Old 26-05-2009
Member
 
Join Date: May 2008
Posts: 390
Re: SQL find and replace query

Hi,

Try this code:

Code:
update [table_name] set [field_name] = replace ([field_name], '[string_to_find ]','[ string_to_replace]');
Reply With Quote
  #3  
Old 26-05-2009
Member
 
Join Date: May 2008
Posts: 209
Re: SQL find and replace query

Hi,

The following query to the SQL server to generate for SP 2.

Create FindReplace stored procedure
Code:
create PROCEDURE FindReplace 
( 
@ TABLE VARCHAR (200), 
@ Field VARCHAR (200), 
@ WHERE VARCHAR (100), 
@ Find VARCHAR (500), 
@ REPLACE VARCHAR (500) 
) 
AS 
DECLARE @ query VARCHAR (8000) 
SET @ query = 'UPDATE' + @ TABLE + 
'SET' + @ Field + '= REPLACE (CONVERT (varchar (8000),' 
+ @ Field +'),''' + @ Find +''',''' + @ REPLACE +''')' 
IF (@ WHERE <>'') 
SET @ query = @ query + 'WHERE' + @ WHERE 

EXECUTE (@ query) 
GO
Reply With Quote
  #4  
Old 26-05-2009
Member
 
Join Date: May 2008
Posts: 249
Re: SQL find and replace query

In order to search and replace text before sending it over to the SQLi buffer, use the following in your ~ /. Emacs:

(Require 'sql)

Code:
 (define-key sql-mode-map (kbd "Cc Cb") 'my-sql-replace-and-send) 
    
      (defcustom my-sql-replacements 
        '(("# USER # "." 'Crueger' ")) 
        "Strings to replace." 
        : type '(repeat (cons (string: tag "search") (string: tag "replace ")))) 
    
      (defun my-sql-replace-and-send () 
        (interactive) 
        (let * ((start (save-excursion 
		        (backward-paragraph) 
		        (point))) 
	       (end (save-excursion 
		      (forward-paragraph) 
		      (point))) 
	       (statement (buffer-substring start end)) 
	       (things my-sql-replacements) 
	       (buf sql-buffer)) 
	  (with-temp-buffer 
	    (insert statement) 
	    (dolist (thing things) 
	      (goto-char (point-min)) 
	      (while (search-forward (car thing) nil t) 
	        (replace-match (cdr thing)))) 
	    (let ((sql-buffer buf)) 
	      (sql-send-buffer )))))
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "SQL find and replace query"
Thread Thread Starter Forum Replies Last Post
Noob question - phpadmin search and replace sql query - Please HELP jackpotsoftware Software Development 1 18-10-2011 04:00 PM
Query to find out duplicate records Shophia_D Software Development 5 22-12-2009 11:03 AM
What is query to find-out age using birthdate? roodiii Software Development 3 05-12-2009 02:55 PM
Make a query to find duplicates ASHER Software Development 4 04-12-2009 04:47 PM
Vbscript find replace help Ingmar1979 Software Development 2 09-07-2009 04:33 PM


All times are GMT +5.5. The time now is 01:05 AM.