Find Strings Containing Hex Range

  • phobia42

    SSC Rookie

    Points: 32

    I have a table with a varchar field.  It contains a number of characters causing us issues, and I believe they fall between hex range 0x80 and 0x9F (I believe they are the 32 characters in the cp1252 character set that have different mappings from ISO-8859-1).  I'm trying to identify them using the below query, but it's not working (returning basically every value with a patindex of 1).  Any help would be greatly appreciated!

     

    select top 2000

    A.*,

    patindex('%[\x80-\x9F]%' COLLATE Latin1_General_BIN, MyField) AS PatIndexPosition

    from MyTable A

    where patindex('%[\x80-\x9F]%' COLLATE Latin1_General_BIN, MyField) > 0

  • Eddie Wuerch

    SSChampion

    Points: 12375

    Embed your search argument in a variable, and use that in the function:

    -- sample data 
    CREATE TABLE #Testr(id int not null primary key, MyField varchar(128))
    GO
    INSERT #Testr(id, MyField)
    SELECT object_id,
    -- using a modulo on the checksum of a NEWID() value to decorate ~10%
    -- of the sample rows with a target character
    name + case when abs(checksum(newid())) % 10 = 0 then char(130) else '' end
    FROM sys.objects
    GO

    -- embed the search args in a variable
    DECLARE @srch varchar(100) = '%[' + char(0x80) + '-' + char(0x9f) + ']%'

    -- use the variable to search
    SELECT id, a.MyField, patindex(@srch COLLATE Latin1_General_BIN, MyField) AS PatIndexPosition
    FROM #Testr A
    WHERE patindex(@srch COLLATE Latin1_General_BIN, MyField) > 0

    • This reply was modified 4 weeks ago by  Eddie Wuerch. Reason: added more comments to the code

    Eddie Wuerch
    MCM: SQL

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply