Home » SQL & PL/SQL » SQL & PL/SQL » How To Get JV/34 as JV/0034 or JV/034 as JV/0034 or JV/0034 as JV/0034 using regular expression
How To Get JV/34 as JV/0034 or JV/034 as JV/0034 or JV/0034 as JV/0034 using regular expression [message #647117] Wed, 20 January 2016 00:28 Go to next message
jainarayan5484
Messages: 38
Registered: August 2006
Location: DELHI
Member
How To Get JV/34 as JV/0034 or JV/034 as JV/0034 or JV/0034 as JV/0034 using regular expression of oracle

thanks in advance
Re: How To Get JV/34 as JV/0034 or JV/034 as JV/0034 or JV/0034 as JV/0034 using regular expression [message #647120 is a reply to message #647117] Wed, 20 January 2016 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Assuming the data arre always in the form "<something>/<a number>":
SQL> with
  2    data as (
  3      select 'JV/34' v from dual
  4      union all
  5      select 'JV/034' v from dual
  6    ),
  7    split as (
  8      select v,
  9             substr(v,1,instr(v,'/')) first_part,
 10             to_number(substr(v,instr(v,'/')+1)) last_part
 11      from data
 12    )
 13  select v, first_part||to_char(last_part,'fm0000') res
 14  from split
 15  /
V      RES
------ ------------
JV/34  JV/0034
JV/034 JV/0034

Re: How To Get JV/34 as JV/0034 or JV/034 as JV/0034 or JV/0034 as JV/0034 using regular expression [message #647138 is a reply to message #647120] Wed, 20 January 2016 04:53 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Dear,

Please use solution given by Michel Sir.Please find same o/p using Regexp.


with
      data as (
        select 'JV/34' v from dual
        union all
        select 'JV/034' v from dual
        union all
        select 'JV/0034' v from dual
      ),
      split as(
       select v,
              regexp_substr(v,'[^/]([A-Z]|[a-z])*') as first_part,
              regexp_substr(v,'[0-9].*') as last_part
       from data
       )
    select  v,first_part,last_part,first_part || '/' || lpad(last_part,4,0) as str
    from    split



[Updated on: Wed, 20 January 2016 05:08]

Report message to a moderator

Re: How To Get JV/34 as JV/0034 or JV/034 as JV/0034 or JV/0034 as JV/0034 using regular expression [message #647149 is a reply to message #647138] Wed, 20 January 2016 09:15 Go to previous message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Or much simpler:

with data as (
              select 'JV/34' v from dual union all
              select 'JV/034' v from dual union all
              select 'JV/0034' v from dual
             )
select  v,
        regexp_replace(regexp_replace(v,'/','/000'),'\d*(\d{4})','\1') new_v
  from  data
/

V       NEW_V
------- ----------
JV/34   JV/0034
JV/034  JV/0034
JV/0034 JV/0034

SQL> 


SY.
Previous Topic: db link between 10g and 12c
Next Topic: Unable to pass 1st value into the query
Goto Forum:
  


Current Time: Fri Apr 19 17:19:27 CDT 2024