• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9156
  • Last Modified:

get string between two characters

I need to get part of a string between the first and second underscores. Example: '1234_test_5678'.  Need to get 'test'. I tried regular expressions, but the query would return an error since I also have values with no underscores or with only one underscore.

 select ltrim(REGEXP_SUBSTR('1234_test_5678', '(^[^-]+)|(-[^-]+)',1,
 REGEXP_COUNT('1234_test_5678', '(^[^-]+)|(-[^-]+)')-1),'-') from dual

  select ltrim(REGEXP_SUBSTR('1234 test', '(^[^-]+)|(-[^-]+)',1,
 REGEXP_COUNT('1234 test', '(^[^-]+)|(-[^-]+)')-1),'-') from dual
(ORA-01428: argument '0' is out of range)
0
teaone
Asked:
teaone
  • 2
1 Solution
 
sdstuberCommented:
if your version of Oracle supports subexpressions...

select REGEXP_SUBSTR('1234_test_5678','_([^_]*)_',1,1,null,1) from dual;


if you can't use subexpressions then use trim

select trim(both '_' from REGEXP_SUBSTR('1234_test_5678','_[^_]*_')) from dual;
0
 
sdstuberCommented:
using the trim method is easier to work with strings that may or may not have underscore delimited content


SELECT  TRIM(BOTH '_' FROM REGEXP_SUBSTR(s, '_[^_]*_|^[^_]*$'))
  FROM (SELECT '1234_test_5678' s FROM DUAL
        UNION ALL
        SELECT '1234 test' FROM DUAL);
0
 
teaoneAuthor Commented:
excellent, thank you!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now