Start with a function that will remove special characters from a string. We'll actually approach this from the other point of view, we'll specify the characters that we'll allow using this syntax. Note the trailing space, that's important. The ^ indicates 'any single character'
This is the complete function:
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'ddf_RemoveNonAlphaNumeric')
DROP FUNCTION ddf_RemoveNonAlphaNumeric
GO
CREATE FUNCTION ddf_RemoveNonAlphaNumeric (@InputVariable AS varchar(max))
-- to test this, run this script:
-- SELECT dbo.ddf_RemoveNonAlphaNumeric('ab cd 01 23 /.,') as MyFunction
Returns varchar(max)
AS
begin
Declare @allowed as varchar(50)
--characters that are allowed. Note the trailing space, so, spaces are allowed
Set @allowed = '%[^a-z0-9 ]%'
While PatIndex(@allowed, @InputVariable) > 0 begin
Set @InputVariable = Stuff(@InputVariable, PatIndex(@allowed, @InputVariable), 1, '')
end
Return @InputVariable
end
go
grant exec on ddf_RemoveNonAlphaNumeric to public
Then, we'll need a trigger to call the proc. Since we're modifying the table that the trigger is attached to, notice that we have to use TRIGGER_NESTLEVLE to keep it from calling itself.
IF OBJECT_ID ('dbo.ddt_PM00200_INSUPD','TR') IS NOT NULL
DROP TRIGGER dbo.ddt_PM00200_INSUPD
GO
CREATE TRIGGER dbo.ddt_PM00200_INSUPD
ON pm00200
after INSERT, UPDATE
AS
if TRIGGER_NESTLEVEL() > 1 begin
return
end
update PM00200 set vndchknm = dbo.ddf_RemoveNonAlphaNumeric(ins.VNDCHKNM)
from inserted ins
join pm00200 v on v.DEX_ROW_ID = ins.DEX_ROW_ID
where ins.VNDCHKNM <> dbo.ddf_RemoveNonAlphaNumeric(ins.VNDCHKNM)
GO