A field format is as follows: XXXXXX [2016] no. 1, XXXXXX [2017] 10, now I need to deal with at the end of the serial number, lack of the three is 0, such as no. 1 to no. 001, to 010, validate the process should be how to write?

CodePudding user response:

 
SQL> 
SQL> Create table test (name varchar (30)); 
The Table created 
SQL> The begin 
2 insert into the test values (' XXXXXX [2016] no. 1 '); 
3 the insert into the test values (' XXXXXX '[2017] 10); 
4 the end; 
5/
PL/SQL procedure successfully completed 
SQL> Select regexp_substr (name, '[^]] +', 1, 1) | | '] '| | lpad (regexp_substr (name,' [^]] + ', 1, 2), 3 '0') | | ', '
2 the from the test; 
REGEXP_SUBSTR (NAME, '[^]] +' 
 1-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
XXXXXX [2016] no. 001 
XXXXXX [2017] no. 010 
SQL> Drop table test purge; 
Table dropped 

SQL>