Link to home
Start Free TrialLog in
Avatar of leap29
leap29Flag for Afghanistan

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of adathelad
adathelad
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial