Processing the TEXT datatype with TSQL

[Steve]

Hi;

I have a table with a TEXT datatype.
Its a comment field.

Right now the users who put in singlequotes are killing the web front
end.

The programmer responsible is fixing this issue but it might be a few
weeks until we get the patch.

I would like to write a trigger that whenever this field is updated it
will scan the text for single quotes ( and hard returns \r ) and
extract them.

I found some nice string functions in HELP.

Will these string functions work with the TEXT datatype in a TSQL
script/trigger?

Thanks in advance

Steve

[Jerry Boone]

I handle quotes with the REPLACE function. All languages that I work with
has it.

Two single quotes in a row signify an escape sequence from the normal
interpretation of the single quote character. When two single quotes appear
together, they are interpreted by SQL as one literal single quote. All we
need do, then, is replace any single quote with two single quotes in strings
that we want interpreted literally by SQL.

This won't work on a Text datatype, however it does work on varchars and
stuff. Check your max len() on that field and see if it actually is using
more than the capacity of other datatypes and see about changing it to
varchar or something. This t-sql replaces one quote with two and would save
your web person endless hours of javascript'ing validation code!!

Select REPLACE(testColumn, char(39), char(39) + char(39)) as texta from
myTable

After all, quotes are valid characters too!!!

Good luck!

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Steve" <st**********@yahoo.com> wrote in message
news:6f**************************@posting.google.c om...

Hi;

I have a table with a TEXT datatype.
Its a comment field.

Right now the users who put in singlequotes are killing the web front
end.

The programmer responsible is fixing this issue but it might be a few
weeks until we get the patch.

I would like to write a trigger that whenever this field is updated it
will scan the text for single quotes ( and hard returns \r ) and
extract them.

I found some nice string functions in HELP.

Will these string functions work with the TEXT datatype in a TSQL
script/trigger?

Thanks in advance

Steve