-
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?
-
sample
<?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('%' + 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--
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|