Help for a Newbie Please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help for a Newbie Please

Author

Topic

maxmum
Starting Member

3 Posts

Posted - 2004-10-11 : 06:45:01

Hello

I am trying to get a statement together that sorts through all the postcodes in the database, and returns all the postcodes that are only numeric, and any that are less than three characters long (excluding 'ROI'.)

Could anyone help please?

Basically I need it to display every postcode that is not a valid UK postcode, UK postcodes are made up of characters and number IE.
NW1 4RY is a valid UK postcode and should not be returned.

Thank you.

Kristen
Test

22859 Posts

Posted - 2004-10-11 : 07:14:38

You'd probably be better to do something in a client language that supports Regular Expressions, and then you could kill off lots of crud that gets through your "only numeric" test, but is still a rubbish postcode!

SELECT *
FROM MyTable
WHERE MyPostCode NOT LIKE '%[A-Z]%'

will find the ones that don't have at least one alpha in them.

You could probably beef this up to

WHERE MyPostCode NOT LIKE '[A-Z][A-Z0-9]% [0-9][A-Z][A-Z]'

if it is true [I'm making an educated guess, although I do know there are some weird formats, and even the basic UK ones are not as standard as, say, Canadian post codes] that a post code must start with an alpha, be followed by either alpha or numeric, then some other wibble, then a space and a digit-alpha-alpha combination

Kristen

maxmum
Starting Member

3 Posts

Posted - 2004-10-11 : 07:23:26

Wicked Kristen, thats a great help, thanks very much.

I now need to add to exclude if thwe postcode is less than 3 characters long, UNLESS that 3 characters are 'ROI'

Kristen
Test

22859 Posts

Posted - 2004-10-11 : 07:30:11

I reckon you could go for killing anything shorted than 7 characters (assuming space is always included) for a UK post code, that would take care of RoI too ...

WHERE MyPostCode NOT LIKE '[A-Z][A-Z0-9]% [0-9][A-Z][A-Z]'

will reject anything shorted than 6 characters ... so we just need to let RoI through:


WHERE     MyPostCode NOT LIKE '[A-Z][A-Z0-9]% [0-9][A-Z][A-Z]'
      AND MyPostCode <> "ROI" [Edit: Plonker!]
      AND MyPostCode <> 'ROI'

Kristen

maxmum
Starting Member

3 Posts

Posted - 2004-10-11 : 09:12:26

Thats great, thanks for your help I really appreciate it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources