Write a T-SQL query that filters records containing NCHAR(2028)

Question

My ultimate goal is to write a sql script that selects data from a particular table where a nvarchar(max) column contains the character NCHAR(2028).

But the obvious:

select * 
from tablename
where columnname like '%' + NCHAR(2028) + '%'

returns all rows.

Answer 1

I think you're hitting limitations on characters that are outside of your collation. I had some weird behavior. Notice the result of the two SELECTs here:

CREATE TABLE dbo.foo
(
    id INT IDENTITY(1,1),
    bar NVARCHAR(128)
);

INSERT dbo.foo(bar) SELECT N'foobar'
UNION 
SELECT N'foo' + NCHAR(2028) + N'bar'

SELECT *
    FROM dbo.foo
    WHERE bar LIKE N'%' + NCHAR(2028) + '%';

TRUNCATE TABLE dbo.foo;

INSERT dbo.foo(bar)
SELECT N'foo' + NCHAR(2028) + N'bar'

SELECT *
    FROM dbo.foo
    WHERE bar LIKE N'%' + NCHAR(2028) + '%';

DROP TABLE dbo.foo;

Notice that whether we've inserted one row or two, we always get the first row back, even though the query is the same and the data has changed.

Unfortunately pasting the actual value of NCHAR(2028) into SSMS doesn't work because it is not in the set of supported characters (I get a glyph like a question mark box in Super Mario Brothers). Otherwise I would just suggest:

WHERE columnname LIKE N'%߬%';

If you can do that from your code (and not worry about SSMS), it may be a workable alternative.

Answer 2

This works

CREATE TABLE #temp (columnname NVARCHAR(128))

INSERT #temp VALUES ('a')
INSERT #temp VALUES ( NCHAR(2028))
INSERT #temp VALUES ('b')
INSERT #temp VALUES ('c' +  NCHAR(2028) + 'c')
 INSERT #temp VALUES ('a' + NCHAR(2028) + 'b') 





 SELECT  *
FROM #temp
WHERE ColumnName COLLATE Latin1_General_Bin Like N'%' + NCHAR(2028) + '%'

drop table  #temp

Answer 3

Use a binary collation for your like comparison.

select * 
from tablename
where columnname COLLATE Latin1_General_Bin like '%' + NCHAR(2028) + '%'