|
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 |
|
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 |
|
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
|
|
|
|