33827

What is the best way to replace a string with another string on entire table?

Question:

My question is similar to below but I want to replace it for any column:

<a href="https://stackoverflow.com/questions/5060526/postgresql-replace-all-instances-of-a-string-within-text-field" rel="nofollow">postgresql - replace all instances of a string within text field</a>

for instance - replace all instances of cat with dog in entire table(all rows)

I am expecting something like below query to work for all columns(without specifying the column name in update query).

UPDATE table_name SET information_schema.columns replace (information_schema.columns, 'old_value', 'new_value');

which actually doesn't seems to work.

Any suggestions please ??

Answer1:

create table sample (s varchar,s1 varchar,s2 varchar,s3 varchar,s4 varchar); insert into sample values ('A','A','A','A','A'); insert into sample values('AB','AB','A','AB','AB'); insert into sample values('A','AB','A','AB','A'); insert into sample values('CA','A','A','CA','CA'); select * from sample

And try like this

create or replace function f1(_table text,_oldVal text,_newVal text) returns void as $$ declare rw record; begin for rw in select 'UPDATE '||$1||' SET '||C.COLUMN_NAME||' = REPLACE ('||C.COLUMN_NAME||','''||$2||''','''||$3||'''); ' QRY FROM (select column_name from information_schema.columns where table_schema='public' and table_name =$1)c loop EXECUTE rw.QRY; end loop; end; $$language plpgsql

And Call

select f1('sample','A','Z') select * from sample <hr />

OR

do $$ declare rw record; begin for rw in select 'UPDATE sample SET '||C.COLUMN_NAME||' = REPLACE ('||C.COLUMN_NAME||',''Z'',''A''); ' QRY FROM (select column_name from information_schema.columns where table_schema='public' and table_name ='sample')c loop EXECUTE rw.QRY; end loop; end; $$; select * from sample

Answer2:

UPDATE doesn't work that way. You need to specify each of the columns you want to change with column = value expressions, separated by a comma.

i.e.

UPDATE foo SET bar = 1, baz = 2 WHERE bar = 5;

Here is more info about <a href="http://www.postgresql.org/docs/current/static/sql-update.html" rel="nofollow">UPDATE</a>, including the grammar.

Answer3:

You're going to have to write some code. You'll need to retrieve the names of all tables and all the names that are textual:

select distinct table_name, column_name from information_schema.columns where table_schema='public' and (data_type like 'char%' or data_type='text') order by table_name, column_name;

and then <strong>for each table/column combination</strong>, build a query to make the replacement:

myQuery = "update " + table_name + " set " + column_name + " = replace(" + column_name + ", 'old_val', 'new_val')"

... and then execute the query in whatever language you're using. You could write this in plpgsql, but seriously, don't. This is a really dangerous piece of code.

If you're looking at doing this for a Wordpress or Drupal site, visit these guys: <a href="https://interconnectit.com/products/search-and-replace-for-wordpress-databases/" rel="nofollow">https://interconnectit.com/products/search-and-replace-for-wordpress-databases/</a>

Recommend

  • CONTAINS doesn't work with Oracle Text
  • Adding MySQL Trigger: invisible (to me) Syntax Error
  • Why does the memory alignment change within a structure?
  • subassignment of selected layers in raster brick
  • Sensor StepCounter sometime not work well on SamSung device
  • VBA Go to the next filtered cell
  • How to separate (split) string with comma in SQL Server stored procedure
  • How can I enforce uniqueness rules that include more than one table?
  • Does Perl currently (5.8 and 5.10) make any promises about the order alternations will be used?
  • MS SQL Server 2008 :Getting start date and end date of the week to next 8 weeks
  • Regex: Match everything except backreference
  • How to find data from last week in MySQL
  • SSLRead() return error -9806/15958)
  • What distributed message queues support millions of queues?
  • How does extglob work with shell parameter expansion?
  • C - formatting MAC address
  • Python getting common name from URL using ssl.getpeercert()
  • Identifier too long in Oracle
  • Reading a file into a multidimensional array
  • Eliminate partial duplicate rows from result set
  • Android application: how to use the camera and grab the image bytes?
  • How to clear text inside text field when radio button is select
  • Scrapy recursive link crawler
  • does jqgrid support a multiple checkbox list for editing
  • PHP - How to update data to MySQL when click a radio button
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • Convert array of 8 bytes to signed long in C++
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • Comma separated Values
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Error creating VM instance in Google Compute Engine
  • Hits per day in Google Big Query
  • How do you join a server to an Active Directory (domain)?
  • Understanding cpu registers
  • how does django model after text[] in postgresql [duplicate]
  • How does Linux kernel interrupt the application?
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices