November 27, 2008 at 5:55 am
Can someone please help.
How do I check for incorrect values in the passportfield, since the passport number may have both characters and integers. There are values captured in this field that shouldn't be such as...
N/A, 12345, ABC
I need to write a query to find these values and have them resolved.
The query below returns passportnumbers as well:
SELECT Headcountdec.Cluster,
Headcountdec.Consolidate,
Headcountdec.Level3,
Headcountdec.Division,
Headcountdec.Region,
Headcountdec.Branch,
Headcountdec.BranchID,
Staff.Staffno,
Headcountdec.CallName,
Headcountdec.Surname,
Staff.NationalID,
LEN(Staff.NationalID) AS NationalIDLenth,
Staff.Passport,
LEN(Staff.Passport) AS PassportLenth
FROM Staff INNER JOIN Headcountdec
ON Staff.Staffno = Headcountdec.StaffNo
AND Staff.Period = Headcountdec.Period
WHERE (Staff.Period = 200810)
AND NOT (ISDATE(LEFT(Staff.Passport, 6)) = 1)
AND (Staff.NationalID = '')
AND (Headcountdec.CallName NOT BETWEEN '0' AND '9')
AND (Headcountdec.CallName NOT IN ('Assessment', 'Fica', 'SACP Imaging', 'PPM', 'Teller', 'E-RM', 'NCCC'))
AND (Headcountdec.CallName NOT LIKE 'Train%')
AND (Headcountdec.CallName NOT LIKE 'Test%')
AND (Headcountdec.CallName NOT LIKE 'DCAR%')
AND (Headcountdec.CallName NOT LIKE 'Retail%')
AND (Headcountdec.Surname NOT LIKE 'Train%')
AND (Headcountdec.Surname NOT LIKE 'Returned%')
AND (Headcountdec.Surname NOT LIKE 'Silica%')
AND (Headcountdec.BranchID NOT LIKE 'FPB%')
Please Help.
November 27, 2008 at 6:27 am
Not sure that I understand what you aim at.
But if you want to filter in terms of a patterns like "999-XXX-99" where "9" is a placeholder for a number and "X" for a character, you might use something like
... like '[0-9][0-9][0-9]-[A-Z][A-Z][A-Z]-[0-9][0-9]'
(not considering collation or unicode/non-unicode issues...)
For further information look at the online help for the LIKE operator.
November 27, 2008 at 6:57 am
basicaly what happens here is that when I run this query I get the results below. When you look at those results, you will realise that the four lines below are not passport. So in my query I need to search for such values.
Passport
AN269339
AN743277
NZ566056D
AN258632
N/A
british
ABC
1234567
November 27, 2008 at 8:10 am
I do not know what your national passport numbers look like but from the examples you show the requiredments are two characters followed by six numbers optionally followed by something else. In that case an appropriate filter would be
like '[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9]%'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply