CODEWITHSUNDEEP
sqlsql-server
asmgx
asmgx

Reputation: 8014

Search text by ASCII code in SQL Server for ASCII 63

There is a special char in my table records (many of them)

It is this char ◾

When I try to search for records that contain this char I get all the records for some reason.

This is my query

SELECT TOP 100 * 
FROM X2 
where txt like N'%◾%'

I found ASCII = 63

I wonder if there is a way I can search in text with ASCII code?

I tried this but get no records

SELECT TOP 100 * 
FROM X2 
where txt like N'%' + char(63) + '%'

Any idea how to get that?

I am using SQL Server 2019 (v15)

Upvotes: 0

Views: 909

Answers (1)

Dale K
Dale K

Reputation: 27224

This is a result of how SQL Server does a Unicode compare and in short can be resolved by changing the collation e.g.

declare @Test table (test nvarchar(max));

insert into @Test (test)
values (N'a◾b'), ('asd');

select *
from @Test
where Test like N'%◾%' COLLATE Latin1_General_100_CI_AI;

Returns:

test
a◾b

Modify the collation to suit e.g. CI (Case Insensitive) <-> (Case Sensitive), AI (Accent Insensitive) <-> AS (Accent Sensitive).

For a full description see this post.

Upvotes: 3

Related Questions