December 12, 2013 at 12:11 am
Hi,
My requirement is to validate the data,as per users specification,
In this,we have the set of data(details), number wise as given in following table(#temp)
Create table #temp
(
Number varchar(15),
openDate varchar(10),
DOB varchar(10),
pincode varchar(40),
PAN_no varchar(40),
Voter_ID varchar(40),
PassPortNo varchar(40),
Telephone varchar(10)
)
Insert Into #temp
Values ('1','01012011','3112013','600042','2FMPJ5610G','CHY4115062','F8050881','9898989898'),
('2','01012011','3112013','600042','AFMPJ5610G','CHY4115062','F8050881','124512'),
('3','01012011','3112013','600042','AFMNJ5610G','CHY4115062','F8050881','9898989898')
Select * from #temp
Following are the Validations,
1) For openDate,
>> Must be later than the Date of Birth field of the Base Segment
>> Must be on or earlier than the date in the Date of Last Payment field of the Base Segment
>> Must be on or earlier than the date in the Date Closed field of the Base Segment
>> Must be on or earlier than the date in the Date Reported field of the Base Segment
2) For PAN No,
If present, must be a minimum of 10 character (excluding
delimiters such as space, hyphen etc.).
The first five characters must be letters, followed by four
numbers, and followed by a letter. The fourth letter must be
either P,F,R,C,A,H,B,J or L
3)For Voter_ID,
If present, must be a minimum of 10 character characters
(excluding delimiters such as space, hyphen etc.).
At least the first two, and no more than the first three
characters must be letters, and the remaining must be
numbers.
4)For PassPortNo,
If present, must be a minimum of 7 characters (excluding
the delimiters such as space, hyphen etc.).
The first character must be a letter and remaining must be
numbers.
5)For Telephone number,
If present must be minimum of 5 digits.
Telephone number must not start with '1'.
If the data contributor provides more than one telephone
number then it should be separated by any of the following
delimiters.
Forward slash "/"
Backward slash "\"
Comma "," or by
Ampersand "&" sign
I want to validate the data as per above validation and give the list of validated data to users,
Desired Output
Number columnname Remark
1 PAN_no First five letters should be alphabet
3 PAN_no The fourth letter must be either P,F,R,C,A,H,B,J or L
2 Telephone Telephone number must not start with '1'.
3 openDate Must be later than the Date of Birth field of the Base Segment
Please Suggest me,how to do this.
Thanks in Advance!
December 12, 2013 at 12:26 am
You can do this by writing queries (obviously).
However it is not our duty to your job in your place. I'm willing to help with specific questions, but not with "here's a list of requirements, please do my job for me".
What do you have so far?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 12, 2013 at 12:34 am
look like a assignment for you Koen :hehe:
December 12, 2013 at 12:38 am
twin.devil (12/12/2013)
look like a assignment for you Koen :hehe:
I'll send my hourly rate... 😛 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 12, 2013 at 12:39 am
Hi Koen,
I don't want all the requirement to be done by you,
I just described by whole requirement,
I just want the approach or any hint ,for how to proceed.
December 12, 2013 at 12:47 am
avdhut.k (12/12/2013)
Hi Koen,I don't want all the requirement to be done by you,
I just described by whole requirement,
I just want the approach or any hint ,for how to proceed.
I'll start with number 2: you can use the LEN function to check for the length (use REPLACE for filter out unwanted characters) and the LIKE clause to check the formatting.
I have to say though a lot of your requirements are easier implemented in .NET than TSQL. In .NET you can use for example regular expressions, something that is not natively present in TSQL.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 12, 2013 at 12:57 am
Koen Verbeeck (12/12/2013)
twin.devil (12/12/2013)
look like a assignment for you Koen :hehe:I'll send my hourly rate... 😛 😀
hmmmm .... one should look for a bank for loan 😛
December 12, 2013 at 1:05 am
Koen Verbeeck (12/12/2013)
avdhut.k (12/12/2013)
Hi Koen,I don't want all the requirement to be done by you,
I just described by whole requirement,
I just want the approach or any hint ,for how to proceed.
I'll start with number 2: you can use the LEN function to check for the length (use REPLACE for filter out unwanted characters) and the LIKE clause to check the formatting.
I have to say though a lot of your requirements are easier implemented in .NET than TSQL. In .NET you can use for example regular expressions, something that is not natively present in TSQL.
Just for an addition to koen, you can implement all the desired logic in a UDF, and just return true or False value for each column. as you only wanted to validate the data. and yes it would be easier in .NET, CLR can be a option for u
December 12, 2013 at 7:03 am
avdhut.k (12/12/2013)
Hi,My requirement is to validate the data,as per users specification,
...
What's the context? Is this user data entry, data import, data migration? The context will determine how best this should be done.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply