How do I query SEDE for user names with ZALGO Unicode abuse?

Question

How do I find user names with several run-on zero-width diacritics in Stack Exchange Data Explorer?

Related question about diacritics at DBA SE: How To Strip Hebrew Accent Marks

My present work-in-progress: https://data.stackexchange.com/stackoverflow/query/446513 (points to a test case in AboutMe, not in DisplayName).

Rationale: being slightly miffed at ZALGO abuse.

Zalgo in user names and effects on comments rendering

Answer 1

I'm not sure how to query for abuse because that would require a kind of fuzzy algorithm that can consume the vague rules about allowed usernames and the content of the database.

I assume your real question is:

How can I query the displayname of the users table that contain combining characters.

This query does that by doing a where clause with the LIKE operator for every single combining character from the UNICODE tables. That requires a somewhat lenghty where clause:

select -- top 10
       id as [User Link]
from users
where  displayname like N'%'+ nchar(0x300) + N'%' collate Latin1_General_BIN   
--  many, many, many, many more lines like that
or displayname like  N'%'+ nchar(0xfe2e) + N'%' collate Latin1_General_BIN 
or displayname like  N'%'+ nchar(0xfe2f) + N'%' collate Latin1_General_BIN 

To generate the where clause based on the tables shown in the wikipedia article I used this helper query and copied its result into the earlier mentioned main query:

-- diac1
select top 112
      'or displayname like N''%''+ nchar(0x' + Format(0x02ff+seq,'x') + ') + N''%'' collate Latin1_General_BIN'
      from (select row_number() over (order by id) as seq
      from votes) as seqlist
union  all    
select top 15
      'or displayname like  N''%''+ nchar(0x' + Format(0x1aaf+seq,'x') + ') + N''%'' collate Latin1_General_BIN'
      from (select row_number() over (order by id) as seq
      from votes) as seqlist
union  all    
select top 64
      'or displayname like  N''%''+ nchar(0x' + Format(0x1dbf+seq,'x') + ') + N''%'' collate Latin1_General_BIN'
      from (select row_number() over (order by id) as seq
      from votes) as seqlist
union  all    
select top 33
      'or displayname like  N''%''+ nchar(0x' + Format(0x20cf+seq,'x') + ') + N''%'' collate Latin1_General_BIN'
      from (select row_number() over (order by id) as seq
      from votes) as seqlist
union  all    
select top 16
      'or displayname like  N''%''+ nchar(0x' + Format(0xFE1f+seq,'x') + ') + N''%'' collate Latin1_General_BIN'
      from (select row_number() over (order by id) as seq
      from votes) as seqlist

When run for Stack Oveflow, after a little patience, the result of the main query looks like this :

This should give you enough information to inspect the 103 results to judge which of those is considered abusive.