Verify field is UK National Insurance number 2

[jeffwest2]

I am having that friday feeling, can anyone give me a poiter for checking if a field is a valid National Insurance number (AA123456A).

I seem to be having a total mind block on how to do this, and i can't find anything that makes sense on the interweb and i just want to go home.

Any help would be very appreciated.

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.

 

[djj55]

[A-Z][A-Z][0-9]....

How about using a Pattern?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

How do you want to implement this check?

You could add a check constraint to the table. This would make sure that all values in that column are either NULL or a valid National Insurance Number.

The code to add the check constraint would be...

Alter Table [!]YourTableName[/!]
Add Constraint UKNationalInsuranceNumber 
Check ([!]YourColumnName[/!] Like '[a-z][a-z][0-9][0-9][0-9][0-9][0-9][0-9][a-z]')

Prior to adding the constraint, you'll need to make sure that all of the data already in the table conforms to the requirements.

You can find 'bad' data with this:

Select *
From   [!]YourTableName[/!]
Where  [!]YourColumnName[/!] Not Like '[a-z][a-z][0-9][0-9][0-9][0-9][0-9][0-9][a-z]'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLScholar]

According to this:

http://www.sage.co.uk/sage1000v2_1/modular_help/py/validation_of_statutory_numbers.htm#NINO

The last letter must be A-D or a space.

Also there are only some combinations that the first two can be - but it doesnt say what.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[jeffwest2]

I could go down the constraint route however I do want the data actually added to the table no matter what, if it doesn't have a valid NINO then I will need to send it to another table to do other stuff to it.

Think I will just need to add a column to the table next to the nino saying which is a valid NINO Y or no and do an update where the NINO filed doesn't match the not in etc etc.

Thanks guys, sorted my Friday out.

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.

 

[SQLScholar]

Aha.

Here is the details for better validation

http://en.wikipedia.org/wiki/National_Insurance_number

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[jeffwest2]

The first two can't be TP,CP,CZ and a couple of others I think, the last letters ate normally between a & d, i can deal with that by looking at where the first two are one of those, although most of those are not proper NINO's anyway and should be included in the dataset i will be using, but thanks for the reminder on that.

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.