Validate data [message #608860] |
Tue, 25 February 2014 10:28 |
|
m.abdulhaq
Messages: 254 Registered: April 2013 Location: Ajman
|
Senior Member |
|
|
i want to put a validation on user input, i have a field where user has to enter the data in specified format like first three leters of char data type and the next four number and then next 4 number, like 'RAP-1234-0001' ,'RAP-1234-0002', but the problem is he is entering it like 'RAP-1234-0001A' OR 'RAP-1234-0002B' ,how can i stop him from entering such values as i am picking the last 4 numbers for generating the next number.
create table pack_list (p_no varchar2(30))
insert into pack_list (p_no) values ('RAP-1234-0001' )
insert into pack_list (p_no) values ('RAP-1234-0002' )
insert into pack_list (p_no) values ('RAP-1234-0001A' )
insert into pack_list (p_no) values ('RAP-1234-0002B' )
SELECT LPAD(NVL(MAX(SUBSTR(P_NO,-4)),1)+1,4,'0') LAST_NO FROM
PACK_LIST; --not returning rows invalid number
|
|
|
|
|
|
Re: Validate data [message #608878 is a reply to message #608874] |
Wed, 26 February 2014 00:59 |
|
Michel Cadot
Messages: 68624 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote: how can i master the art of regular expressions from basic
There are multiple tutorials on the web.
Quote: i want to take the last number extracted from this string how can i do it.
Assuming the format is the one in your first post and your example covers all the cases:
SQL> select p_no, regexp_substr(p_no,'(\d+).?$',1,1,'c',1) from pack_list;
P_NO REGEXP_SUBSTR(P_NO,'(\D+).?$',
------------------------------ ------------------------------
RAP-1234-0001 0001
RAP-1234-0002 0002
RAP-1234-0003A 0003
RAP-1234-0004B 0004
[Updated on: Thu, 27 February 2014 00:42] Report message to a moderator
|
|
|
|
|
|
|
|