In SQL Server, how can I check if a field contains a character outside of a specific range?

Question

I'm trying to write a query that will check if a single character within an nvarchar field is outside of a valid range.

I thought a query like this would work, but the results include both rows instead of only the invalid one:

select foo
from (
    select N'ABDC' as foo
    union all 
    select N'ABǼC' as foo
) as bar
where bar.foo like '%[^ -~]%' COLLATE SQL_Latin1_General_Cp1_CS_AS

The valid range is anything within ASCII space and the tilde.

A simpler example is also failing:

select foo
from (
    select N'ABDC' as foo
    union all 
    select N'ABzC' as foo
) as bar
where bar.foo like '%[^A-Z]%' COLLATE SQL_Latin1_General_Cp1_CS_AS

This returns no results while this returns the correct row:

select foo
from (
    select N'ABDC' as foo
    union all 
    select N'AB~C' as foo
) as bar
where bar.foo like '%[^A-Z]%' COLLATE SQL_Latin1_General_Cp1_CS_AS

Here's a fiddle of the simple example: http://sqlfiddle.com/#!18/9eecb/93202

What am I doing incorrectly?

score 0 · Accepted Answer · answered Jul 30 '20 at 15:57

I was able to make this work using LIKE based on this answer:

select foo
from (
    select N'ABDC' as foo
    union all 
    select N'ABǼC' as foo
) as bar
where bar.foo COLLATE Latin1_General_BIN like N'%[^ -~]%' COLLATE Latin1_General_BIN

Looks like I just needed a different collation.

Similarly, this works as well:

select foo
from (
    select N'ABDC' as foo
    union all 
    select N'ABǼC' as foo
) as bar
where patindex('%[^ -~]%' COLLATE Latin1_General_BIN,bar.foo) > 0

In SQL Server, how can I check if a field contains a character outside of a specific range?

1 Answers1