Home » SQL & PL/SQL » SQL & PL/SQL » Validation of PAN Number in sql query (Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production)
Validation of PAN Number in sql query [message #586278] Wed, 05 June 2013 02:48 Go to next message
amarjadhav
Messages: 40
Registered: April 2011
Location: bangalore
Member

Hi all,

create TABLE pan_number(pan CHAR(10) NOT NULL);

INSERT INTO pan_number(pan) VALUES('ABCDE1234A');

INSERT INTO pan_number(pan) VALUES('FGHIG5678F');

INSERT INTO pan_number(pan) VALUES('ABCDE12345');

INSERT INTO pan_number(pan) VALUES('ABCD1234A');

select * from pan_number;

now i need to validate valid PAN number which is of " The first 5 letters should be alphabets & last letter as alphabet & total length of PAN no. should be 10 digit"

i need to display valid PAN number , only first two rows are valid PAN numbers.

so can u please help me
Re: Validation of PAN Number in sql query [message #586279 is a reply to message #586278] Wed, 05 June 2013 02:52 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Hi,

You can use REGEXP_LIKE for your pattern matching and LENGTH for checking the length of each code.

Regards,
Dariyoosh
Re: Validation of PAN Number in sql query [message #586283 is a reply to message #586279] Wed, 05 June 2013 03:09 Go to previous messageGo to next message
amarjadhav
Messages: 40
Registered: April 2011
Location: bangalore
Member

Hi Dariyoosh,
SELECT pan
FROM pan_number
WHERE REGEXP_LIKE(pan,'[A-Z][A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][A-Z]');
By using above query i got the required output.
Thanks a lot
Re: Validation of PAN Number in sql query [message #586285 is a reply to message #586283] Wed, 05 June 2013 03:15 Go to previous messageGo to next message
Littlefoot
Messages: 21579
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Close! You never said that - after 5 letters - the following 4 "characters" are digits. Are they? Or is abcdeXXXXf valid as well?
Re: Validation of PAN Number in sql query [message #586288 is a reply to message #586278] Wed, 05 June 2013 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
regexp_like(col, '^[[:alpha:]]{5}....[[:alpha:]]$')

Regards
Michel

[Updated on: Wed, 05 June 2013 03:21]

Report message to a moderator

Re: Validation of PAN Number in sql query [message #586289 is a reply to message #586285] Wed, 05 June 2013 03:21 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
@amarjadhav

In addition to what Littlefoot said, the current pattern accept only capital letters. So instead of [A-Z] you may want to use [a-zA-Z] (unless the requirement was only capital letters).

Also [A-Z][A-Z][A-Z][A-Z][A-Z] can be written as [A-Z]{5}

Regards,
Dariyoosh

[Updated on: Wed, 05 June 2013 03:22]

Report message to a moderator

Re: Validation of PAN Number in sql query [message #586290 is a reply to message #586289] Wed, 05 June 2013 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
[a-z] are also alphabetic characters.

Regards
Michel
Re: Validation of PAN Number in sql query [message #586294 is a reply to message #586290] Wed, 05 June 2013 03:29 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Yes, that's why I told him that his current pattern REGEXP_LIKE(pan,'[A-Z][A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][A-Z]'); does not accept them.

In addition, depending on NLS_CHARACTERSET, [ [:aplha:] ] is not always the same thing as [a-zA-Z].

Regards,
Dariyoosh

[Updated on: Wed, 05 June 2013 03:30]

Report message to a moderator

Re: Validation of PAN Number in sql query [message #586317 is a reply to message #586294] Wed, 05 June 2013 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In addition, depending on NLS_CHARACTERSET, [ [:aplha:] ] is not always the same thing as [a-zA-Z].


Correct (and I didn't say this was the case, just give example of alphabetic characters that are not in [A-Z]).
More clear specification have to be provided but this is just a detail, the solution is there.

Regards
Michel
Re: Validation of PAN Number in sql query [message #586333 is a reply to message #586285] Wed, 05 June 2013 05:37 Go to previous messageGo to next message
amarjadhav
Messages: 40
Registered: April 2011
Location: bangalore
Member

@Littlefoot

Yes, first five characters are alphabets,4 characters are digits and last one character is alphabet (totally 10 characters)

SELECT pan
FROM pan_number
WHERE REGEXP_LIKE(pan, '^:alpha:{5}....:alpha:$');
then this will not properly in some case like "ABCDEFG34A"

SELECT pan
FROM pan_number
WHERE REGEXP_LIKE(pan, '^:alpha:{5}:digit:{4}:alpha:$');
this is working fine

Thanks a lot for all
Re: Validation of PAN Number in sql query [message #586335 is a reply to message #586289] Wed, 05 June 2013 05:39 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
dariyoosh wrote on Wed, 05 June 2013 13:51

So instead of [A-Z] you may want to use [a-zA-Z] (unless the requirement was only capital letters).


You can always use ignore case(i), instead of mentioning as [a-zA-Z]
Re: Validation of PAN Number in sql query [message #586338 is a reply to message #586335] Wed, 05 June 2013 05:52 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
lalitcool555 wrote on Wed, 05 June 2013 12:39
dariyoosh wrote on Wed, 05 June 2013 13:51

So instead of [A-Z] you may want to use [a-zA-Z] (unless the requirement was only capital letters).


You can always use ignore case(i), instead of mentioning as [a-zA-Z]


And did the OP tell you that he wanted to ignore the letter case?? As Michel said, more information is to be provided, not by me, not by you, but by the OP.


Regards,
Dariyoosh
Re: Validation of PAN Number in sql query [message #586340 is a reply to message #586333] Wed, 05 June 2013 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
then this will not properly in some case like "ABCDEFG34A"


This works properly for the requirements you gave.

Regards
Michel
Re: Validation of PAN Number in sql query [message #678666 is a reply to message #586278] Thu, 26 December 2019 03:58 Go to previous messageGo to next message
Sivasai506
Messages: 4
Registered: December 2019
Junior Member
Could you please explain insert time how to verified?
Re: Validation of PAN Number in sql query [message #678667 is a reply to message #678666] Thu, 26 December 2019 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide.

I failed to see how this could be related to the current topic?
I also failed to understand your question.
Please create a new topic (click on "NEW TOPIC" button top-right the forum and explain in details your issue.
Don't forget to post your Oracle version (with 4 decimals like 12.2.0.1).

Re: Validation of PAN Number in sql query [message #678674 is a reply to message #678666] Fri, 27 December 2019 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Given your other question, you (or rather Oracle) verify a value at INSERT time adding a CHECK constraint to your table column.

[Updated on: Fri, 27 December 2019 01:40]

Report message to a moderator

Re: Validation of PAN Number in sql query [message #678706 is a reply to message #678674] Mon, 30 December 2019 06:44 Go to previous message
Bill B
Messages: 1968
Registered: December 2004
Senior Member
And another variation

SELECT pan
FROM pan_number
WHERE REGEXP_LIKE(pan, '^[A-Z]{5}.{4}[A-Z]{1}$');

using this regular expression the following strings are valid

ABCDE1234A
FGHIG5678F

and the following strings arew invalid
ABCDE12345
ABCD1234A

If the positions from 6-9 need to be a number you can use

SELECT pan
FROM pan_number
WHERE REGEXP_LIKE(pan, '^[A-Z]{5}[0-9]{4}[A-Z]{1}$');

[Updated on: Mon, 30 December 2019 06:48]

Report message to a moderator

Previous Topic: Trying to delete bunch of users using loop but failing
Next Topic: Pan card insert time validation (3 merged)
Goto Forum:
  


Current Time: Fri Aug 07 23:23:02 CDT 2020