Remove characters in an ASCII decimal range: How To?
in [Programming SQL]
|
From: Sylvain Lafontaine on 17 May 2010 16:20 While investigating this   stuff with XML Path, I've found that returning the result of the subquery as an XML object using the TYPE option and then converting this object into a string get rid of these entities; so the following query seems to work correctly on SQL-Server 2008: 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, Cast ((SELECT CASE WHEN ASCII(SUBSTRING(statement_tx, n, 1)) BETWEEN 0x00 AND 0x1F THEN '' ELSE SUBSTRING(statement_tx, n, 1) END + '' FROM test_row AS B JOIN Nums ON n <= LEN(statement_tx) WHERE B.statement_nr = A.statement_nr Order by Nums.N FOR XML PATH(''), TYPE) as varchar(256)) AS DesNM_clean FROM test_row AS A) UPDATE UpdateCTE SET statement_tx = DesNM_clean; -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "Tom Cooper" <tomcooper(a)comcast.net> wrote in message news:%232iFsuR9KHA.5476(a)TK2MSFTNGP06.phx.gbl... > 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 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: Sylvain Lafontaine on 17 May 2010 16:22 Just out of curiosity, do you really intend to use this XML Path solution instead of using an UDF in a production database? -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "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 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
First
|
Prev
|
Pages: 1 2 Prev: Remove characters in an ASCII decimal range: How To? Next: Send Mail to Multiple recipients |