how to validate and split PAN no

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to validate and split PAN no

Author

Topic

kirank
Yak Posting Veteran

58 Posts

Posted - 2010-08-13 : 06:11:58

hi

i want to validate and split PAN no with following condition plz help

PAN CARD PATTERN -
1. START WITH ALPHABET (A-Z)
2. ALPHABET SHOULD BE 5 CHARACTER IN LENGH
3. THEN 4 DIGITS (0-9)
4. LAST CHARACTER SHOULD BE ALPHABET(A-Z) 5. All characters must be in Uppercase

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-08-13 : 07:42:13

If I understand your requirement clearly then this should work for you:


DECLARE @T TABLE
(
   pan CHAR(10) NOT NULL CHECK(pan LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][A-Z]'),
   pan_alpha_prefix AS LEFT(pan, 5),
   pan_alpha_digits AS SUBSTRING(pan, 6, 4),
   pan_alpha_suffix AS RIGHT(pan, 1)   
);

INSERT INTO @T(pan)
   VALUES('ABCDE1234A');

INSERT INTO @T(pan)
   VALUES('FGHIG5678F');

INSERT INTO @T(pan)
   VALUES('ABCDE12345');

INSERT INTO @T(pan)
   VALUES('ABCD1234A');

SELECT *
  FROM @T;

kirank
Yak Posting Veteran

58 Posts

Posted - 2010-08-13 : 07:51:06

how i can use this
ex --

PAN no is : ABCDD1234D

how i can check with this pattern in my query

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-08-13 : 07:57:42

I modified my previous reply to show you that you should apply the CHECK on the data in the table. But if you can't or if you are cleaning your data you can use something like this:


DECLARE @T TABLE
(
   pan CHAR(10) NOT NULL
);

INSERT INTO @T(pan)
   VALUES('ABCDE1234A');

INSERT INTO @T(pan)
   VALUES('FGHIG5678F');

INSERT INTO @T(pan)
   VALUES('ABCDE12345');

INSERT INTO @T(pan)
   VALUES('ABCD1234A');

SELECT pan,
       LEFT(pan, 5) AS pan_alpha_prefix,
       SUBSTRING(pan, 6, 4) AS pan_alpha_digits,
       RIGHT(pan, 1) AS pan_alpha_suffix
  FROM @T
 WHERE pan LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][A-Z]';

kirank
Yak Posting Veteran

58 Posts

Posted - 2010-08-13 : 08:29:34

thax , can u give me function of same
so i can only pass the pan number and it will return true or false

thanx a lot :)

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-08-13 : 08:52:37

I highly recommend that you do not use scalar-valued functions. But if you have to, you can use this:


CREATE FUNCTION udf_CheckPAN(@pan CHAR(10))
RETURNS TINYINT
AS
BEGIN
	RETURN CASE WHEN @pan LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][A-Z]'
                    THEN 1
               ELSE 0
          END;
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources