Friday, 22 May 2015

Regular Expression to append space .

REGEXP_REPLACE :

The following query places a space between Oracle its version using REGEXP_REPLACE function. For example, Oracle9i will become Oracle 9i, Oracle10g will become Oracle 10g. We are looking for a series of alphabets and take them as group 1. Then we are looking for a group of digits followed by any character and treat it as group 2. Then we replace the original with \1 (group 1) a space and \2 (group 2). 
select regexp_replace('Oracle10g','([[:alpha:]]+)([[:digit:]]+.)','\1 \2') from dual;

Example: 
select 'Oracle10g' ACTUAL_DATA,   regexp_replace('Oracle10g','([[:alpha:]]+)([[:digit:]]+.)','\1 \2') Transformed_Data from dual;



REGULAR EXPRESSION TO ADD LEADING AND TRAILING SPACE TO NON-ALPHA NUMERIC CHARACTERS EXCEPT $ :
Query :
select '||||a$' ACTUAL_DATA, 
regexp_replace('||||a$','([^[:alnum:]$])',' \1 ') Transformed_Data from dual;




No comments:

Post a Comment