Bad characters in strings

11 Nov 2005 5:52 PM

....

Hi

We have a problem whereby a particular application is writing certain ascii
characters to our table which is causing an issue in another application.
Obviously the solution is to fix the application, but it's taking some time.
In the meantime I'm trying to develop a trigger to fix the data. I have a
table which contains a row for every ascii character which we do not want to
allow, I'm trying to develop a very efficient trigger to remove these bad
ascii characters (on around 7 string columns in 1 table)

Any hints on how to do this?
Thanks

11 Nov 2005 6:06 PM

Tony Rogerson

Use an INSTEAD OF trigger rather than an AFTER (the default), the INSTEAD OF
will do it before the insert/update occurrs thereby making it more
efficient, if you did it in the AFTER trigger (the default) then you'll be
doing an insert and then a further delete.

Use nested REPLACE to remove the characters, do you have a list of the
characters - are there a lot?

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

Show quote

"...." <.**@nowhere.com> wrote in message
news:O2Joviu5FHA.472@TK2MSFTNGP15.phx.gbl...
> Hi
>
> We have a problem whereby a particular application is writing certain
> ascii characters to our table which is causing an issue in another
> application.
> Obviously the solution is to fix the application, but it's taking some
> time. In the meantime I'm trying to develop a trigger to fix the data. I
> have a table which contains a row for every ascii character which we do
> not want to allow, I'm trying to develop a very efficient trigger to
> remove these bad ascii characters (on around 7 string columns in 1 table)
>
> Any hints on how to do this?
> Thanks
>
>
>
>