If you want to remove line breaks or other characters from Oracle fields, the following article might help.

One scenario is for example export of data into text files.

SQL

translate(' example ', chr(10) || chr(13) || chr(09), ' ') 

The above will replace all line breaks (chr(10)), all tabs (chr(09)) and all carriage returns (chr(13)) with a space (' ').

The Oracle function 'translate' is applied on the whole String and might show better performance than regular expressions depending on the complexity of your regex.

Enjoy.

Last Updated: 28 September 2016

Hits: 21368

I had to rename a column in a table in an oracle database.

SQL

Unfortunately the oracle documentation was not working for me and I had to play around a bit and search the internet until I found the following syntax to be working.

alter table tableName rename column oldName to newName;

Some things to watch out for.

You have to use simple column names.

The oracle documentation listed as first result in a google search provided the following example, which did not work for me.

RENAME COLUMN EMPLOYEE.MANAGER TO SUPERVISOR

I am puzzled as to why the above syntax did not work for me while the alter table statement worked fine, since I was able to create the table in the first place

It looks as if I just misinterpreted the oracle documentation and the rename statement always has to be combined with an alter table statement.

References

Oracle documentation

https://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqljrenamecolumnstatement.html

Another resource using a syntax, which worked for me.

http://www.dba-oracle.com/t_rename_column_table.htm

Last Updated: 24 April 2015

Hits: 10599

Splitting Strings is a trivial task. Using SQL unfortunately it is not so easy to do. Recently I had to split long concatenated strings from one field in an Oracle database.

Luckily I was not the only one facing this challenge and found helpful information on the internet.

Below an example that did the trick for me.

 -- Splitting test strings --
WITH qry AS (
Select 'a,b,c' As tf From Dual
Union Select 'd,e,f' As tf From Dual 
Union Select 'g,h' As tf From Dual 
Union Select 'i' As tf From Dual
) 
SELECT regexp_substr(tf, '[^\,]+', 1, rn) Transposed_Field 
FROM qry 
cross join 
(
Select rownum rn from ( 
select max(regexp_count(tf, ',')) + 1 mx from qry) connect by level <= mx 
) 
Where regexp_substr(tf, '[^\,]+', 1, rn) is not null
Order By Transposed_Field;

The resultset of above query will look like the following picture.

Last Updated: 01 January 2018

Hits: 15385