CODEWITHSUNDEEP
postgresqlreplacepsql
Telefonmann
Telefonmann

Reputation: 315

How to replace a single character in every record for every table + column in the entire database with an empty string?

my problem is the following:

I have a bibliographic database that was started around 1995 and has gone through several conversion into different formats, the latest is PSQL. Due to automatic conversion and differing styles of entering data by my predecessors I have many remnants of older noting techniques, which are not only obsolete now, but also pollute the database.

E.g. people used to note empty information as "-" (hyphen) instead of leaving it blank, which is now becoming a problem since I have many entries where the subtitle is recorded as "-".

Question: For every record in this database I want to delete all strings that only contain a single hyphen in all tables (author, journal article, book monograph ...) and columns (gender, subtitle, supplements ...). Strings like "Pseudo-title - the early years" shouldn't be changed. The record should be altered like this:

Author: "Altmann" -> "Altmann", Title: "Pseudo-title" -> "Pseudo-title", Subtitle: "-" -> ""

How do I do that?

Thanks in advance,

Telefonmann

Upvotes: 0

Views: 124

Answers (2)

Douglas Cardona
Douglas Cardona

Reputation: 151

Try a function to covert all your tables, and repeat the query for every single table in the schema:

CREATE OR REPLACE FUNCTION update_tables(schema IN VARCHAR) RETURNS void AS $$ 
DECLARE statements 
CURSOR FOR SELECT schemaname, tablename FROM pg_tables WHERE  schemaname = schema;
BEGIN FOR stmt IN statements LOOP EXECUTE 'UPDATE TABLE ' || quote_ident(stmt.schemaname) || '.' || quote_ident(stmt.tablename) || ' [put your query conditions here];';
        END LOOP;
    END; $$ LANGUAGE plpgsql;

Upvotes: 1

Rhim
Rhim

Reputation: 674

See String Functions and Operators

One of the variants:

UPDATE you_name_table SET Subtitle = '' WHERE trim(both ' ' from Subtitle) = '-';

Upvotes: 0

Related Questions