Home » SQL & PL/SQL » SQL & PL/SQL » Validate data (Oracle 10g)
Validate data [message #608860] Tue, 25 February 2014 10:28 Go to next message
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 #608862 is a reply to message #608860] Tue, 25 February 2014 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

alter table pack_list add constraint p_no_chk check (regexp_like(p_no,'^[A-Z]{3}-[0-9]{4}-[0-9]{4}$'));


Re: Validate data [message #608874 is a reply to message #608862] Tue, 25 February 2014 22:33 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
Thanks very much Mr.Michael, i have two queries , how can i master the art of regular expressions from basic and another thing is incase if i am allowing the user to input data with alpha numeric and i want to take the last number extracted from this string how can i do it.

for example


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-0003A' )

insert into pack_list (p_no) values ('RAP-1234-0004B' )

-- the last number + 1 is i need.for example i want 

'RAP-1234-0005'



Re: Validate data [message #608875 is a reply to message #608874] Wed, 26 February 2014 00:46 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
how can i master the art of regular expressions from basic

Slowly, by educating yourself. My favourite site is Regular-expressions.info.

Quote:
the last number + 1 is i need

Combine INSTR, SUBSTR, TO_NUMBER, LPAD, TO_CHAR functions, concatenation and addition.
Re: Validate data [message #608878 is a reply to message #608874] Wed, 26 February 2014 00:59 Go to previous messageGo to next message
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

Re: Validate data [message #608921 is a reply to message #608875] Wed, 26 February 2014 22:32 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
thanks Littlefoot , indeed a very informative site.
icon14.gif  Re: Validate data [message #608922 is a reply to message #608878] Wed, 26 February 2014 22:33 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
thanks Michael for the solution , this is what exactly i want.
Re: Validate data [message #609385 is a reply to message #608878] Thu, 06 March 2014 03:53 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
Sorry for disturbing you michael, when i tried to run this its giving error as
ora-00939 : too many arguments for function.

I think it will not work in 10g , its my mistake i did not mention the oracle version while posting the query.
Re: Validate data [message #609391 is a reply to message #609385] Thu, 06 March 2014 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ooops! Sorry, I test it in 11g and indeed in 10g it has not the last parameter.
You can do something like:
SQL> select p_no, regexp_substr(regexp_substr(p_no,'\d+.?$'),'\d+') from pack_list;
P_NO                           REGEXP_SUBSTR(REGEXP_SUBSTR(P_
------------------------------ ------------------------------
RAP-1234-0001                  0001
RAP-1234-0002                  0002
RAP-1234-0003A                 0003
RAP-1234-0004B                 0004

Re: Validate data [message #609443 is a reply to message #609391] Thu, 06 March 2014 20:44 Go to previous message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
thanks Michael.
Previous Topic: how to remove redundant data
Next Topic: unit calculation
Goto Forum:
  


Current Time: Thu Mar 28 03:02:51 CDT 2024