Re: [SQL] search and replace

[Benoît BOURNON]

I do not try, may be it exists different languages to make this in postgresql : I make this in pl/pgsql, I do not have time to make this in C, CREATE OR REPLACE FUNCTION "_replaceblock" (text, text, text) RETURNS text AS' DECLARE  text_begin ALIAS FOR $1 ;  key_name ALIAS FOR $2 ;  key_val ALIAS FOR $3 ;  key_begin VARCHAR(10) := '{' ;  key_end VARCHAR(10) := '}' ;  return_text TEXT ;  return_text_length INTEGER;  find_key_start INTEGER ;  find_key_end INTEGER ;  temp_text TEXT ;  key_search TEXT ;  key_value TEXT ; BEGIN  IF (key_val IS NULL) THEN      key_value :='' ;   ELSE      key_value := key_val ;   END IF ;    return_text := text_begin ;  key_search := key_begin || key_name || key_end ;  find_key_start := position (key_search IN return_text) ;  WHILE (find_key_start >0) LOOP   IF (find_key_start > 0) THEN      find_key_end := find_key_start + length (key_search) ;      find_key_start := find_key_start - 1 ;      return_text_length := length (return_text) ;      temp_text := substring (return_text FROM 1 FOR find_key_start) ;      temp_text := temp_text || key_value ;      temp_text := temp_text || substring (return_text FROM find_key_end) ;      return_text := temp_text ;   END IF ;   find_key_start := position (key_search IN return_text) ;  END LOOP ;  return return_text ; END ; 'LANGUAGE 'plpgsql'; I do not know it exists a contrib to use perl in procedure language ??? ... but you can use TCL if you knows this language. Jodi Kanter wrote: I have a field in one of my tables that has a path to a file listed. I need to move those files and now have to update all those paths. Is there a simply search and replace type option in sql? or do I need to do an update using the entire new path name? If so, I think the easier solution will be to dump the table, use a macro to update it and then restore. Jodi -- _______________________________ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]