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.
| Author |
Topic |
|
MaverickUK
Yak Posting Veteran
89 Posts |
 Posted - 2005-02-21 : 06:40:53
|
Howdy folksAs part of a large project I'm dealing with, I need to verify a table full of UK postcodes. The verifcation at this stage is relitivly simple and is just a check to make sure that they are a certain length, have numbers/letters in the correct places etc..I know that I can do this verification with the following regular expression^[A-Z]{1,2}[0-9]{1,2}[A-Z]{0,1} [0-9][A-Z]{2}$The problem is, MS SQL Server 2000 doesn't have regular expressions built in. Sadly I don't have SQL Server 2003, so I cannot take advantage of the power of .net!I've seen various regexp plug-ins for SQL Server 2000, but they all seem to cost. Sadly, with this project, I cannot turn to a paid for solution.So either* Has anyone performed a postcode verifcation without regular expression?* How difficult would it be for myself to write an extended procedure to perform such a task?Thanks to anyone who can help  |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-02-21 : 07:05:29
|
sql has limited regex capabilities when using LIKE.ie:declare @MyTable table (postCode varchar(20))insert into @MyTableselect '123-456-abc' union allselect '123-def-abc' union allselect '123-a32-rr2' select *from @MyTablewhere postCode like '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[a-zA-Z][a-zA-Z][a-zA-Z]' Go with the flow & have fun! Else fight the flow  |
 |
|
|
MaverickUK
Yak Posting Veteran
89 Posts |
Posted - 2005-02-21 : 09:02:51
|
| Perfect!I'll add this to my list of useful SQL tricks I never knew about, thank you |
|
|
|
|
|
|
UK Postcode verification