?
Solved

get string between two characters

Posted on 2013-01-26
3
Medium Priority
?
7,782 Views
Last Modified: 2013-01-26
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
Comment
Question by:teaone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 38823408
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 38823416
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
 

Author Comment

by:teaone
ID: 38823447
excellent, thank you!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question