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 specific table where the nvarchar (max) column contains the NCHAR character (2028).

But the obvious:

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

returns all rows.

Answer 1

Use binary sort for comparison.

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

Answer 2

It 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

I think you are pushing restrictions on characters that are outside of your sort. I had some strange 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; 

Please note that if we inserted one or two rows, we always return the first row, even if the query is the same and the data has changed.

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

 WHERE columnname LIKE N'%߬%'; 

If you can do this from your code (and not worry about SSMS), this might be an acceptable alternative.