Post reply

Find Strings Containing Hex Range

  • February 28, 2020 at 11:21 pm

    #3729770

    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

  • February 29, 2020 at 10:45 pm

    #3729887

    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 years, 9 months ago by  Eddie Wuerch. Reason: added more comments to the code

    Eddie Wuerch
    MCM: SQL

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

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