Prev: Remove characters in an ASCII decimal range: How To?
Next: Send Mail to Multiple recipients
From: bill on 16 May 2010 01:26
Hi Erland,

I should have explained more thoroughly. The source is a commercial
application that only allows users to enter ASCII 33 - 127 into the
screen field that gets populated into the database column. However,
the source application adds formatting characters for its own display
format purposes that aren't documented.

I don't want those formatting characters, which are outside the range
of 33 - 127. I have found a number of the characters, but I don't
know if I have found them all. I figured it would be most
straightforward to simply exclude characters that are not in the range
33 - 127, instead of hunting samples for formatting characters.

Thanks,

Bill
From: bill on 16 May 2010 01:32
Hi Plamen:

I'm interested in using your query, but as I am only vaguely familiar
with FOR XML PATH, I am not sure how to process the results.

If I use your query on my sample above, I get a result like this:

This is an interrupted string


I was hoping to simply strip the bad characters but have a normal
resulting string with real spaces, etc. I could do a REPLACE, but I
don't know what other character patterns your query will return, so I
don't know how many REPLACE functions to nest.

Is there some type of built-in SQL Server function that understands
the delimiters created by FOR XML PATH and can render the string for
me without the need for a REPLACE?

The query looks great, so a little guidance would be a big help.

Thanks,

Bill
From: Tom Cooper on 16 May 2010 13:17
Hi Bill,
My experience is that you only get the html escapes like   (that's for
a space - and there are other characters that will do the same thing, like
&) when you don't concatenate anything to the

(SELECT CASE WHEN ASCII(SUBSTRING(statement_tx, n, 1))
BETWEEN 32 AND 127
THEN SUBSTRING(statement_tx, n, 1)
ELSE ' '
END -- one way to fix is to concatenate something here
FROM test_row AS B
JOIN Nums
ON n <= LEN(statement_tx)
WHERE B.statement_nr = A.statement_nr
FOR XML PATH('')) AS statement_tx_clean

part of the query. And it doesn't work to concatenate a null string (''),
it has to be a string of one or more characters. The way I would fix this
here is to concatenate a CHAR(128). This leaves extra CHAR(128)'s in your
string, but you can use one replace to get rid of them (since you know they
don't belong). For example:

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';

SELECT statement_tx FROM test_row;

WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
UpdateCTE
AS
(SELECT statement_nr, statement_tx,
(SELECT CASE WHEN ASCII(SUBSTRING(statement_tx, n, 1))
BETWEEN 32 AND 127
THEN SUBSTRING(statement_tx, n, 1)
ELSE ' '
END + Char(128)
FROM test_row AS B
JOIN Nums
ON n <= LEN(statement_tx)
WHERE B.statement_nr = A.statement_nr
FOR XML PATH('')) AS statement_tx_clean
FROM test_row AS A)
UPDATE UpdateCTE
SET statement_tx = Replace(statement_tx_clean,Char(128),'');

SELECT statement_tx FROM test_row;
go
Drop Table test_row;

Tom

"bill" <billmaclean1(a)gmail.com> wrote in message
news:51c9ca7b-2373-4c5f-8227-a4834bf15c5c(a)k31g2000vbu.googlegroups.com...
> Hi Plamen:
>
> I'm interested in using your query, but as I am only vaguely familiar
> with FOR XML PATH, I am not sure how to process the results.
>
> If I use your query on my sample above, I get a result like this:
>
> This&#x20;is&#x20;an&#x20;interrupted&#x20;string
>
>
> I was hoping to simply strip the bad characters but have a normal
> resulting string with real spaces, etc. I could do a REPLACE, but I
> don't know what other character patterns your query will return, so I
> don't know how many REPLACE functions to nest.
>
> Is there some type of built-in SQL Server function that understands
> the delimiters created by FOR XML PATH and can render the string for
> me without the need for a REPLACE?
>
> The query looks great, so a little guidance would be a big help.
>
> Thanks,
>
> Bill

From: bill on 16 May 2010 13:55
Thanks Plamen and Erland. This query cleans up the strings nicely.

Thanks,

Bill
From: Plamen Ratchev on 16 May 2010 22:38
Perhaps still needs "ORDER BY n.Number" to guarantee correct
concatenation order. :)

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2
Prev: Remove characters in an ASCII decimal range: How To?
Next: Send Mail to Multiple recipients