• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 429
  • Last Modified:

Regular expression validation sql insert

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
leap29
Asked:
leap29
1 Solution
 
adatheladCommented:
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now