Find Strings Containing Hex Range

• phobia42

  SSC Enthusiast

  Points: 172

  More actions

  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

• Eddie Wuerch

  SSChampion

  Points: 12541

  More actions

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

  Eddie Wuerch
  MCM: SQL