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.

Details

Parent Category: Oracle Database

Last Updated: 01 January 2018

Read more: Split and Transpose Strings

After missing to add a foreign key to a table, I ended up having to correct my mistake in many places in my PL SQL code.

Afterwards I thought: "Who created this table without this important constraint?! ... :0( Uups! It was me."

If something similar happens to you, below you'll find an SQL to add a foreign key to a table.

'References parent_table_name(parent_field)' gives the parent table, while 'Foreign Key(field_name)' is the field from the child table, which will be referenced.

If you receive an error message saying that the alter table constraint could not be validated because the table has child records, one quick fix is to delete all records in child and parent table and then executing the alter table statement again.

/* Drop a table constraint */
ALTER TABLE sod_cat_app_cat
drop constraint
fk_sod_category_app; /* Name of a constraint */

/*Add a Table Constraints for foreign key*/
Alter Table sod_cat_app_cat Modify (
	Constraint fk_sod_category_app 
	Foreign Key(sod_category_app_id)
	References sod_category_app(ID)
);

Details

Parent Category: Oracle Database

Last Updated: 24 June 2014

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.

Details

Parent Category: Oracle Database

Last Updated: 28 September 2016