Help regarding regexp_substr [message #667296] |
Wed, 20 December 2017 02:25 |
|
POGAKU_SANTHOSH
Messages: 39 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.
Thank you.
[Updated on: Wed, 20 December 2017 02:30] Report message to a moderator
|
|
|
|
Re: Help regarding regexp_substr [message #667301 is a reply to message #667296] |
Wed, 20 December 2017 03:18 |
|
Michel Cadot
Messages: 68625 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
|
|
|
|