SQL Query Help on finding Extended ASCII

I am stumped and can't find the answer on google.

I need to create a query that finds any characters in a text/varchar field that has any extended ASCII characters. We want to remove any extended ASCII characters. This is in SQL 2008 SP1.

Example:
Select * from MyTable
where there is an ascii character in MyField

Thanks in advance,


Edit by admin: no contact info permitted on the forum, thank you

Can you post sample data?

<?xml version="1.0" encoding="utf16"?><maintenanceRequest><modifyObjec ts><exchangePartner><select><sendingRtId>031000040 </sendingRtId><receivingRtId>053104762</receivingRtId></select><fileTypes>ICL,ICLR</fileTypes><exchangeType>N</exchangeType></exchangePartner></modifyObjects></maintenanceRequest>

DECLARE @asciichars TABLE (dec_value TINYINT NOT NULL, char_value AS CHAR(dec_value) COLLATE Latin1_General_CS_AS PERSISTED NOT NULL)

--fill in table var with numbers from 128 to 255 {extended ASCII dec values}
INSERT INTO @asciichars(dec_value)
SELECT rownum
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY object_id) AS rownum
FROM sys.columns
) AS nums
WHERE rownum BETWEEN 128 AND 255


--get records
SELECT DISTINCT t.*, b.*
FROM dbo.MyTable AS t
CROSS APPLY (SELECT a.dec_value, a.char_value, PATINDEX('&#37;' + CHAR(a.dec_value) +'%' COLLATE Latin1_General_CS_AS, t.mycolumn COLLATE Latin1_General_CS_AS) AS thepatindex FROM @asciichars AS a) AS b
WHERE b.thepatindex > 0
AND ASCII(SUBSTRING(t.mycolumn, b.thepatindex ,1)) = b.dec_value


--HTH--