Solved

get string between two characters

Posted on 2013-01-26
3
6,087 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
  • 2
3 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 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 73

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

786 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