postgres query where values contain backslash

Question

I'm in the process of migrating a db from MySql to PostgreSql

I have one last thing outstanding: In the table steps there is a column overlay_custom_css.

Example of data in customer_overlay_css: left:957px;\r\ntop:594px;\r\nwidth:68px;\r\nheight:30px;

I need to remove the \r\n from this data. As I understand it, the \ is a special char so must be escaped by another \

source:https://www.postgresql.org/docs/8.3/static/functions-matching.html

Here's what I have so far:

UPDATE
  steps
SET
  overlay_custom_css = REPLACE(overlay_custom_css,'\\r\\n','')
WHERE
  overlay_custom_css LIKE '%\\r\\n%';

After I run this it says it affected 200+ rows, but looking at my data it made no difference.

Could anyone tell me where I'm going wrong?

Vamsi Prabhala · Accepted Answer · 2017-04-09 20:46:20Z

3

You can use regexp_replace.

select regexp_replace(overlay_custom_css,'\\r\\n','','g')

answered Apr 9, 2017 at 20:46

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brad Over a year ago

Thanks, this worked UPDATE steps SET overlay_custom_css = regexp_replace(overlay_custom_css,'\\r\\n','','g')

Evan Carroll · Accepted Answer · 2017-04-09 20:41:53Z

1

To use the C-style escapes you need to use E''

PostgreSQL also accepts "escape" string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g., E'foo'. (When continuing an escape string constant across lines, write E only before the first opening quote.) Within an escape string, a backslash character () begins a C-like backslash escape sequence, in which the combination of backslash and following character(s) represent a special byte value, as shown in Table 4-1.

so it looks like this,

SELECT x, replace(x, E'\r\n', '')
FROM ( VALUES
  (E'foo\r\nbar')
) AS t(x);

answered Apr 9, 2017 at 20:41

Evan Carroll

1

1 Comment

Brad Over a year ago

Thanks for the answer, This didn't work though, I suspect due to the like clause to work E'%\r\n%' but in anycase, problem solved. Ty

mangonights · Accepted Answer · 2020-05-06 21:05:36Z

0

If using Laravel, use quad-escapes:

// querying for 'Foo\Bar'...
Model::where('foo', 'like', 'Foo\\\\Bar')->get()

edited May 6, 2020 at 21:05

answered May 6, 2020 at 19:37

mangonights

9991 gold badge10 silver badges11 bronze badges

Collectives™ on Stack Overflow

postgres query where values contain backslash

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related