CODEWITHSUNDEEP
regexpostgresqlsql-update
GeoBeez
GeoBeez

Reputation: 1014

replace regex does not work in postgresql

I have a table with a column of string. within the string there are single quote which I want to get rid of all single quotes.for example:

"''hey, hey, we're the monkees''"

my regex works perfect and select all the values containing single quotes.

select regexp_replace(colName, '%''%', '') from tblName;

but it does not update my table when I want to replace this regex with nothing.

UPDATE tblName SET colName = regexp_replace(colName, '%''%', '');

I also checked this one 

UPDATE tblName SET colName = replace(colName, '%''%', '');

Upvotes: 0

Views: 5892

Answers (2)

IMSoP
IMSoP

Reputation: 97718

Different functions and operators in Postgres use one of three different pattern matching languages, as described in a dedicated section of the manual.

The % form you are using here is the SQL LIKE syntax, where % represents "any number of any character". But the function you are using, regexp_replace, expects a Posix regular expression, where the equivalent would be .* (. meaning any character, * meaning repeat zero or more times).

Also note that LIKE expressions have to match the whole string, but a Posix regex doesn't, unless you explicitly match the start of the string with ^ and the end with $.

So the direct translation of '%''%' would be '^.*''.*$', giving you this:

UPDATE tblName SET colName = regexp_replace(colName, '^.*''.*$', '');

In practice, this would give the same effect as the simpler:

UPDATE tblName SET colname='' WHERE colname LIKE '%''%';

Your actual use case is much simpler: you want to replace all occurrences of a fixed string (', which will need to be quoted and escaped as '''') with another fixed string (the empty string, written ''). So you don't need any pattern matching at all, just straight replacement using replace:

UPDATE tblName SET colname=replace(colname, '''', '');

This will probably be faster if you limit it to rows that contain an apostrophe to begin with:

UPDATE tblName SET colname=replace(colname, '''', '') WHERE colname LIKE '%''%';

Upvotes: 2

Rémy Baron
Rémy Baron

Reputation: 1399

% is not an regexp character

try this 

select regexp_replace(colName, $$'$$, '','g') from tblName;

($$ is use to surround your string instead of ' to simplify the query) (,'g') is use to continue after the first quote is found.

 UPDATE tblName SET colName = regexp_replace(colName, $$'$$, '','g');

Upvotes: 0

Related Questions