Showing posts with label SUBSTR. Show all posts
Showing posts with label SUBSTR. Show all posts

Tuesday, 15 January 2013

Oracle SUBSTR (Seperate strings) based on a delimiter

Using regexp_substr we can match a expression in the parent string the delimiter here is '.'
[^.]* --> match one or more occurences of the string [any chars]ending with . so this matches anything like abc. xyz. 123. or even .

select regexp_substr('1.00..60200....','[^.]*.',1,4) from dual;
Result: 60200.

Using the below rtrim we can trim the '.' in the end.

select rtrim('123.','.') from dual;

Final SQL:
select rtrim(regexp_substr('1;00;123;60200;;;;','[^;]*;',1,5),';') seg from dual;
Result: null
select rtrim(regexp_substr('1;00;123;60200;;;;','[^;]*;',1,2),';') seg from dual;
Result: 00
select rtrim(regexp_substr('1;00;123;60200;;;;','[^;]*;',1,3),';') seg from dual;
Result: 123

Integrations Lead - Lessons learnt

 Integrations have been my passion for a while but like anything tech there is no credit given when things go right but always heaps of pres...