Remove characters in an ASCII decimal range: How To?

External Since: Oct 17, 2008 Posts: 17

I have a table that has some prettty nasty strings in it. I would
like to remove all characters outside the range of 33 - 127 decimal
from the string

Here is what I mean:

CREATE TABLE test_row
(
statement_nr INT NOT NULL
,statement_tx VARCHAR(200) NOT NULL
)
ALTER TABLE test_row ADD PRIMARY KEY (statement_nr)

INSERT INTO test_row SELECT 1, 'This string has an embedded
'+CHAR(0)+'null character';
INSERT INTO test_row SELECT 2, 'This string has an embedded
'+CHAR(7)+'bell character';
INSERT INTO test_row SELECT 3, 'This string has an embedded
'+CHAR(128)+'ASCII decimal 128 character';

If you query the table (SELECT statement_tx FROM test_row) you'll get
these results:
This string has an embedded
This string has an embedded bell character
This string has an embedded €ASCII decimal 128 character

Notice the first statement is terminated, because SQL stops when it
hits the null. The other statements (don't know if it comes through
on the newsgroup), show the actual ASCII character immediately after
the space that follows the word 'embedded'

I would like some SQL that strips out all the charcters other than 32
-127. My result set would thus look like:
This string has an embedded null character
This string has an embedded bell character
This string has an embedded ASCII decimal 128 character

I do not want to write a CLR function, and I would prefer to avoid
even a tsql scalar function (I don't much like functions in general).
I am hoping some set-oriented SQL would allow this.

I searched the archives and found a solution (http://
groups.google.com/group/microsoft.public.sqlserver.programming/
browse_thread/thread/f9786bbe4a3ca1e3/e785868b90b50ed1?lnk=gst&q=remove
+extended+ascii#e785868b90b50ed1) from Plamen that used XML path, but
I couldn't get it to work, and don't actually understand XML Path.
Perhaps someone could explain it to me, and tell me how to get rid of
the characters that XML path seems to add without resorting to
REPLACE?

Thanks,

Bill

External Since: May 30, 2004 Posts: 2061

bill ( ) writes:
> I have a table that has some prettty nasty strings in it. I would
> like to remove all characters outside the range of 33 - 127 decimal
> from the string

You don't want to that. You may want to remove non-printable characters,
but believe me, there are plenty of printable characters beyond 127.
For instance, you cannot properly send goods to my address without such a
character. And I can tell you that I get pretty p*ssed when I enter my
address on a web form, and I am told that it contains an illegal character.

I don't know what's in your data, but I think you pretty naïve if you
believe all those characters are junk you want to get rid of. For instance:

This string has an embedded €ASCII decimal 128 character

What if you have a string that goes:

"The price for the service is €200,000."

You stripping venture will delete important information from the string.

> Notice the first statement is terminated, because SQL stops when it
> hits the null.

Not really, but the tools typically takes the NUL as a string terminator.

> I do not want to write a CLR function,

Why not? The CLR is the best tool for the purpose.

> I am hoping some set-oriented SQL would allow this.

Well, you can define a table that includes all characters you want
to remove. Then you can run a cursor over the table and run

replace(targettbl.col, badchars.ch, '')

on it. Not the most efficient, but it could be OK for a one-off.

....and it will not always work. If you have a Windows collation (and
you should), replace on char(0) does not work, because char(0) is
not defined in Unicode. But if you force an SQL collation, replace
will operate on char(0) as well.

> I searched the archives and found a solution (http://
> groups.google.com/group/microsoft.public.sqlserver.programming/
> browse_thread/thread/f9786bbe4a3ca1e3/e785868b90b50ed1?lnk=gst&q=remove
> +extended+ascii#e785868b90b50ed1) from Plamen that used XML path, but
> I couldn't get it to work, and don't actually understand XML Path.
> Perhaps someone could explain it to me, and tell me how to get rid of
> the characters that XML path seems to add without resorting to
> REPLACE?

Plamen's update statement first split the strings into tabular format,
with one row per character, then the strings are reconcatenated with XML
PATH. I think I see two problems with query:

1) There is no ORDER BY, so strings could be reassembled in the wrong
order.
2) The way the query is written, characters like & will be entisised into
& etc. This can be address by using FOR XML PATH(''), TYPE and
then convert the resulting XML to nvarchar.

On the other hand, the way his query is written, there is no risk for
issues like REPLACE.

--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx