x

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.

more ▼

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

avatar image

user-1904
23 1 1 4

(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

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(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

avatar image

user-1904
23 1 1 4

@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.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1069
x502

asked: Jun 02, 2010 at 07:05 AM

Seen: 2090 times

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

Copyright 2016 Redgate Software. Privacy Policy