Remove line break from Oracle Sql database column

Question

I want to remove a line break when selecting a column in an Oracle database.

So far, I have only found

SELECT translate(myCol, chr(10)||chr(13), ' ') from myTable

but that does not return results when making ' ' to ''.

How do I remove the line break and not replace it with another sign?

Try to use the replace function. Replace accepts the empty string as parameter: replace(myCol, chr(13) || chr(10), '') — Viktor Török
– Viktor Török, Commented Feb 24, 2021 at 10:55

Aleksej · Accepted Answer · 2021-02-24 10:53:44Z

1

Pay attention on how translate works: it "translates" single characters, one by one and positionally; for example, this

select translate('abcdefg', 'ac', ' ') from dual

substitutes 'a' with blank and 'c' with nothing, because the second string has nothing in the second position, thus removing 'c'. So, you may need something like:

translate(myCol, 'X' || chr(13) || chr(10), 'X')

This changes 'X' into 'X', chr(13) and chr(10) into nothing, so it just removes chr(10) and chr(13)

Also, notiche that in this way you don't only remove the concatenation of chr(13) and chr(10), but every occurrence of chr(13) and chr(10).

answered Feb 24, 2021 at 10:53

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

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

Collectives™ on Stack Overflow

Remove line break from Oracle Sql database column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related