Finding 'bad' characters

Question

Finding 'bad' characters

Barcelona10

SSCertifiable

Points: 5420
More actions
June 22, 2026 at 8:03 pm

#4871284

Hi All
I am trying to find 'bad' characters that users might type in. My query is finding them as expected, but the results include records that I think my query should not return.
Here is my query
select NOTES , ascii(left(NOTES ,1))
from trip with(nolock)
where (patindex('%[^ !-~]%' collate Latin1_General_BIN, NOTES ) > 0 or
patindex('%[|]%' collate Latin1_General_BIN, NOTES ) > 0)
Unexpected record
CXBC 67
Thank you

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

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

Steve Jones - SSC Editor SSC Guru Points: 742729 More actions · Answer 1

First, it's helpful to give some sample data and DDL that helps understand this.

Second, here's a repro below. What's likely happened is there is some unprintable character that is getting caught in here in some way. This shows me adding tabs or a few other characters.

use sandbox;
go
drop table if exists trip;
go
create table trip
(
    NoteID int not null identity(1, 1)
  , Notes varchar(20)
  , NNotes nvarchar(20)
);
go
insert dbo.trip
(
    Notes
  , NNotes
)
values
(null, null)
, ('CXBC', N'CXBC')
, ('  CXBC', N'   CXBC')
, ('CXBC$', N'CXBC$')
, ('CXBC!', N'CXBC!')
, (char(9) + 'CXBC', char(9) + N'CXBC')
, ('
CXBC', N'
CXBC');
go
select NoteID
     , Notes
     , len(Notes)                                                                                                   as string_len
     , datalength(Notes)                                                                                            as byte_len
     , replace(replace(replace(replace(Notes, ' ', '[SP]'), char(9), '[TAB]'), char(10), '[LF]'), char(13), '[CR]') as visible
     , case
           when
           (
               patindex('%[^ !-~]%' collate Latin1_General_BIN, Notes) > 0
               or patindex('%[|]%' collate Latin1_General_BIN, Notes) > 0
           ) then
               'filtered'
           else
               'raw data'
       end                                                                                                          as DataWithFilter
from trip with (nolock);