Home » SQL & PL/SQL » SQL & PL/SQL » Help regarding regexp_substr (oracle 11g)
Help regarding regexp_substr [message #667296] Wed, 20 December 2017 02:25 Go to next message
POGAKU_SANTHOSH
Messages: 36
Registered: April 2017
Member
Hi,

I have a string 'HIERPOF_ASDFGHJ_20171211_234.TXT' i need to extract a part from the string which is present after 3rd '_' and before the '.' I.E(234). The output should be 234.I tried using following query but can i replace with a regexp_substr ?

query :=

SELECT
SUBSTR (
SUBSTR ('HIERPOF_ASDFGHJ_20171211_234.TXT',
INSTR ('HIERPOF_ASDFGHJ_20171211_234.TXT', '_', -1) + 1),
1,
INSTR (
SUBSTR (
'HIERPOF_ASDFGHJ_20171211_234.TXT',
INSTR ('HIERPOF_ASDFGHJ_20171211_234.TXT', '_', -1) + 1),
'.')
- 1)
AS res
FROM DUAL;


any one can give me an alternate for this query . So that i can simplify it and make it short. Smile

Thank you.

[Updated on: Wed, 20 December 2017 02:30]

Report message to a moderator

Re: Help regarding regexp_substr [message #667299 is a reply to message #667296] Wed, 20 December 2017 03:08 Go to previous messageGo to next message
Littlefoot
Messages: 21358
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If input string always looks like that, then have a look at this:

SQL> with test as (select 'HIERPOF_ASDFGHJ_20171211_234.TXT' col from dual)
  2  select regexp_substr(col, '\d+', 1, 2) result from test;

RES
---
234

SQL>
Re: Help regarding regexp_substr [message #667301 is a reply to message #667296] Wed, 20 December 2017 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 65380
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    data as (select 'HIERPOF_ASDFGHJ_20171211_234.TXT' data from dual),
  3    step1 as (select substr(data, instr(data,'_',1,3)+1) step1 from data)
  4  select substr(step1, 1, instr(step1,'.')-1) classic,
  5         regexp_replace(data, '^[^_]+_[^_]+_[^_]+_([^\.]+)\..*$', '\1') regexp
  6  from step1, data
  7  /
CLA REG
--- ---
234 234
Re: Help regarding regexp_substr [message #667310 is a reply to message #667296] Wed, 20 December 2017 07:13 Go to previous message
Solomon Yakobson
Messages: 2704
Registered: January 2010
Location: Connecticut, USA
Senior Member
with t as (select 'HIERPOF_ASDFGHJ_20171211_234.TXT' str from dual)
select  regexp_substr(str,'^([^_]+_){3}([^.]+)',1,1,null,2) x
  from  t
/

X
---
234

SQL>

SY.
Previous Topic: Oracle 12c injection
Next Topic: create table in another schema
Goto Forum:
  


Current Time: Sun Feb 18 21:53:17 CST 2018

Total time taken to generate the page: 0.02367 seconds