Question 295978 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Escape special characters of column values in single SQL query

I have a table like:

id login amount
22 *@mail.co 230
39 state}.com 340

I need to replace (escape) special characters of login column cells like in this
JS method:
strstr.replace(/[.*+?^${}()|[]\]/g, "\$&").

Using knexjs library with Postgres DB, I tried the next query:

knex('orders')
      .update({
        login: knex.raw(`REPLACE(login, '/[-[]{}()*+?.,\^$|#s]/g', '\$&')`)
        // or another 
        login: knex.raw(`REPLACE(login, regexp_match(login, '/[-[]{}()*+?.,\^$|#s]/g'), '\$&')`)
      })

Both variants returns an error about REPLACE: replace(character varying, text[], unknown) does not exist.

I also tried another method:
update "orders" set "login" = REGEXP_REPLACE(login,'[-[]{}()*+$1.,\^$|#s]', '\$&', 'g'); – it returns an error about invalid Regex. This query works without + sign in the middle:
update "orders" set "login" = REGEXP_REPLACE(login,'[-[]{}()*$1.,\^$|#s]', '\$&', 'g');.

Is it possible (after all) to use regex during such SQL query to escape special symbols?

Tags:

2

Answers


  1. 0

    You’ll need to use the REGEXP_REPLACE function instead of just REPLACE.

    For example:

    knex.raw(`REGEXP_REPLACE(login, '/[-[]{}()*+?.,\^$|#s]/g', '\$&')`)

    Your regex needs to a POSIX regex, not a JS regex. Test it in psql before using it in knex. This cheatsheet may also be useful.

    Login or Signup to reply.
  2. 0 
    knex('orders')
      .update({
        login: knex.raw(`login=regexp_replace(login,'(W)','\\1','g')`)
      })

    The pattern W is shorthand for [^[:word:]], so it’s looking for all non-word characters (word class includes alphanumericals [a-zA-Z0-9] plus underscore _). First four of the five backslashes in replacement produce a double backslash before the hit, which ends up evaluated as a single backslash before the hit. 'g' is there to apply that to all hits.

    Demo at db<>fiddle:

    create table orders (
  id int generated by default as identity primary key,
  login text,
  amount int);

insert into orders (id,login,amount) values 
(22,'*@mail.co',230),
(39,'state}.com',340);

update orders set login=regexp_replace(login,'(W)','\\1','g')
returning *;
    id login amount
    22 *@mail.co 230
    39 state}.com 340

    In PostgreSQL, regular replace() doesn’t support POSIX regular expression syntax, regexp_replace() does.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search