question

user-1904 avatar image
user-1904 asked

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.

Thanks.

t-sqlsql-server-2000
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

user-1904 avatar image
user-1904 answered

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)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
@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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.