Menu
All,
I am stuck again trying to get my data in a format that I need it in. I have a text field that looks like this.
"deangelo 001 deangelo
local origin of name: italain
from the american name deangelo
meaning: of the angels
emotional spectrum • he is a fountain of joy for all.
personal integrity • his good name is his most precious asset. personality • it’s hard to soar with eagles when you’re surrounded by turkeys! relationships • starts slowly, but a relationship with deangelo builds over time. travel & leisure • a trip of a lifetime is in his future.
career & money • a gifted child, deangelo will need to be challenged constantly.
life’s opportunities • joy and happiness await this blessed person.
deangelo’s lucky numbers: 12 • 38 • 18 • 34 • 29 • 16
"
What would the best way be in Postgresql to remove the carriage returns and new lines? I've tried several things and none of them want to behave.
select regexp_replace(field, E'\r\c', ' ', 'g') from mytable WHERE id = 5520805582 SELECT regexp_replace(field, E'[^\(\)\&\/,;\*\:.\>\<[:space:]a-zA-Z0-9-]', ' ') FROM mytable WHERE field~ E'[^\(\)\&\/,;\*\:.\<\>[:space:]a-zA-Z0-9-]' AND id = 5520805582; Thanks in advance, Adam
426
The LTRIM() function removes all characters, spaces by default, from the beginning of a string. The RTRIM() function removes all characters, spaces by default, from the end of a string. The BTRIM function is the combination of the LTRIM() and RTRIM() functions.
In Postgresql, we can replace the special characters as well such as @, #, $ using the replace function. Let's replace the email containing a special character. SELECT replace('[email protected]','@','at'); In the above code, we are replacing the @ character in the email with the word at.
To use "escape sequences" in a string literal you need to use an "extended" constant. You can specify the escape character by prefixing the letter E: UPDATE posts SET body = E'First Line\nSecond line.
select regexp_replace(field, E'[\\n\\r]+', ' ', 'g' ) read the manual http://www.postgresql.org/docs/current/static/functions-matching.html
98
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
