leap29
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
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,'(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.