Solved

get string between two characters

Posted on 2013-01-26
3
5,457 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now