Compare part of a string against a table of values

Question

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.

Thanks.

Answer 1

0

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

answered Jun 02 '10 at 07:18 AM

Fatherjack ♦♦
38.8k ● 56 ● 73 ● 104

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 '10 at 08:16 AM user-1904

(comments are locked)

Answer 2

0

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)

answered Jun 02 '10 at 08:18 AM

user-1904
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 '10 at 08:53 AM Fatherjack ♦♦

(comments are locked)

Compare part of a string against a table of values

Follow this question

Related Questions