Prev: Update order
Next: Remove characters in an ASCII decimal range: How To?
From: bill on 15 May 2010 00:20
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
 | 
Pages: 1
Prev: Update order
Next: Remove characters in an ASCII decimal range: How To?