If you are able to do so, restrict your inserts into a stored proc where you can replace the incoming string before you insert. But if you do want to use a trigger, you can do as shown below.
You need a numbers table for the code below. If you don't have one, create one like this - it should have as many numbers as the maximum number of characters you expect in the input string.-- create a numbers table.
CREATE TABLE N(n INT NOT NULL PRIMARY KEY CLUSTERED);
;WITH N(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM N WHERE n < 100)
INSERT INTO N SELECT n FROM N OPTION (MAXRECURSION 0);
Then, create the trigger like shown below. I am creating a test table for creating the trigger first.-- create a test table
CREATE TABLE dbo.TblA (x VARCHAR(32));
Now your trigger would be like shown below:CREATE TRIGGER dbo.TblATrigger ON dbo.TblA
INSTEAD OF INSERT
AS
DECLARE @pattern VARCHAR(32)= '[^a-zA-Z]';
INSERT INTO dbo.TblA
SELECT c FROM
(
SELECT
x AS [text()]
FROM
(
SELECT
SUBSTRING(INSERTED.x,n,1) AS x
FROM
N
CROSS JOIN INSERTED
WHERE
n <= LEN(INSERTED.x)
AND SUBSTRING(INSERTED.x,n,1) NOT LIKE @pattern
)s FOR XML PATH('')
)T(c)
GO
remove illigal characters
Posted - 11/05/2012 : 15:24:04
