Results 1 to 4 of 4

Thread: SQL Query Help on finding Extended ASCII

  1. #1
    Join Date
    Apr 2010
    Posts
    2

    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

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Can you post sample data?

  3. #3
    Join Date
    Apr 2010
    Posts
    2

    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>

  4. #4
    Join Date
    Sep 2005
    Posts
    168
    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--

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •