� IN SQL Server database

Question

in my database I have this char �. I want to locate them with a query

Select * 
from Sometable 
where somecolumn like '%�%'

this gets me no result.

I think it is ANSI encoding

Answer 1

use N like below

 where col like N'%�%'

why do you think ,you need N prefix:

Prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.

Thanks to Martin Smith,Earlier i tested only with one character earlier and it worked,but as Martin pointed out, it returns all characters..

Below query works and returns only intended

select * from #demo where id  like N'%�%' 
COLLATE Latin1_General_100_BIN

Demo:

create table #demo
(
id nvarchar(max)
)

insert into #demo
values
(N'ﬗ'),
( N'�')

to know more about unicode,please see below links

http://kunststube.net/encoding/

https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/

Answer 2

This is the Unicode replacement character symbol.

It could match any of 2,048 invalid code points in the UCS-2 encoding (or the single character U+FFFD for the symbol itself).

You can use a range and a binary collate clause to match them all (demo).

WITH T(N)
AS 
(
SELECT TOP 65536 NCHAR(ROW_NUMBER() OVER (ORDER BY @@SPID))
FROM master..spt_values v1, 
     master..spt_values v2
)
SELECT N 
FROM T
WHERE N LIKE '%[' +  NCHAR(65533) + NCHAR(55296) + '-' + NCHAR(57343) + ']%' COLLATE Latin1_General_100_BIN

Answer 3

You can use ASCII to find out the ascii code for that char

Select ascii('�')

And use CHAR to retrieve the char from that code and combine it in a LIKE expression

Select * from Sometable
where somecolumn like '%'+CHAR(63)+'%'

Note the collation you use can affect the result. Also it depends on the encoding used by your application to feed your data (UTF-8, UNICODE, etc). also how you store it VARCHAR, or NVARCHAR has a last say on what you see.

There's more here in this similar question

EDIT @Mark

try this simple test:

create table sometable(somecolumn nvarchar(100) not null)
GO

insert into sometable
values
 ('12345')
,('123�45')
,('12345')
GO

select * from sometable
where somecolumn like '%'+CHAR(63)+'%'
GO

This only means that character was stored win the as a "?" in this test.

When you see a � it means the app where you are seeing isn't quite sure what to print out.

It also mean OP probably needs to find out what char is that using a query. Also note it means a string outputted like ��� can be 3 formed by different characters.

CHAR(63) was just an example, but you are right this in the ASCII table will be a standard interrogation.

EDIT @Bridge

Not with time right now to deep dig in it but the below test don't worked

Select ascii('�'), CHAR(ascii('�')), UNICODE(N'�'), CHAR(UNICODE(N'�'))
GO

create table sometable(somecolumn nvarchar(100) not null)
GO

insert into sometable
values
 ('12345')
,('123�45')
,('12345')
,('12'+NCHAR(UNICODE(N'�'))+'345')
GO

select * from sometable
where somecolumn like '%'+CHAR(63)+'%'

select * from sometable
where somecolumn like '%'+NCHAR(UNICODE(N'�'))+'%'

GO