Compare part of a string against a table of values

I have a table with a field containing UK National Insurance numbers. These numbers are two letters followed by six numbers and a final letter ie AA999999A.

The first two letters are not random, and in fact come from a 'master list' of about 230 combinations. I have all of these combinations in another table.

How can I compare the first two characters in the national insurance number to check that they are valid (present in the master list)?

The database is SQL Server 2000 but I can move the data into a 2005 database if there are better functions available in that release.


more ▼

asked Jun 02, 2010 at 07:05 AM in Default

user-1904 gravatar image

23 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You can find those that are not valid by using a JOIN between the two tables.

SELECT NINo from NINoTable as n LEFT JOIN NINoReference as nr on left(n.NINo,2) = nr.Chars where nr.Chars is null 
more ▼

answered Jun 02, 2010 at 07:18 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

Marked as answer (can't upvote yet as not enough rep here). I did it a different was in the end (detailed as another answer) but your methid seems more elegant to me. Thanks!
Jun 02, 2010 at 08:16 AM user-1904
(comments are locked)
10|1200 characters needed characters left

For reference here is an alternative method (the one I used in the end, although I prefer Fatherjack's solution)

select NINo from NINoTable where substring(NINo, 1,2) not in (select chars from NINoReference) 
more ▼

answered Jun 02, 2010 at 08:18 AM

user-1904 gravatar image

23 1 1 2

@marko, thanks for your comments. The difference to keep in mind between our respective solutions is that using the JOIN method will be very quick, especially if there are indexes (hopefully PK-FK) on the joined columns. The solution would scale well if the tables are 000,000's of rows whereas your method will see a big performance hit as data size grows. thanks for posting your solution though, its good to see alternatives and be able to compare them.
Jun 02, 2010 at 08:53 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 02, 2010 at 07:05 AM

Seen: 1463 times

Last Updated: Jun 02, 2010 at 07:05 AM