Solved

# Regular expression validation sql insert

Posted on 2009-02-20
376 Views
I have created a CLR Function that determines if a postcode is a valid uk one.  I've used to clean up the data in my table.  I'm confused how I can now use this to validate any new postcodes inserted in to my table.  I can't validate it on the front end as our DB was bought off the shelf so I can't do it that way.  How can I add a validation rule on insert?  like somthing alongthe lines below -

Eg

ALTER PROCEDURE [dbo].[LOCATION_INSERT]
(
@postcode            NVARCHAR(20),
REST OF THE VARIABLES etc..)
AS
BEGIN
INSERT INTO LOCATIONS(
POSTCODE,
REST OF THE COLOUMNS ETC...)
VALUES (
@POSTCODE,
REST OF THE VALUES ETC...)

WHERE POSTCODE  =dbo.RegexMatch(l2.code,'(^[A-Za-z]{1,2}[\d]{1,2}([A-Za-z])?\s?[\d][A-Za-z]{2}\$)') = 1
0
Question by:leap29
1 Comment

LVL 23

Accepted Solution

Hi,

You can add a CHECK constraint on the POSTCODE column. This is an example very nearly identical to what you are trying to do and gives you the step by step instructions:
http://davidhayden.com/blog/dave/archive/2006/04/18/2917.aspx

Or, you could do it in the sproc:
ALTER PROCEDURE [dbo].[LOCATION_INSERT]
(
@postcode            NVARCHAR(20),
REST OF THE VARIABLES etc..)
AS
BEGIN
IF dbo.RegexMatch(l2.code,'(^[A-Za-z]{1,2}[\d]{1,2}([A-Za-z])?\s?[\d][A-Za-z]{2}\$)') <> 1
RAISERROR('Invalid PostCode', 16, 1)
ELSE
BEGIN
INSERT INTO LOCATIONS(
POSTCODE,
REST OF THE COLOUMNS ETC...)
VALUES (
@POSTCODE,
REST OF THE VALUES ETC...)
END
END
0

## Featured Post

### Suggested Solutions

In SQL 2008 how do I get a Pivot list of columns 5 33
SQL - sum months, quarter, YTD 9 67
SQL Query 2 44
Stored procedure 4 21
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.