SQL Server - Group by on nvarchar data showing Chinese and Japanese characters as qusetion marks

Asked By Nancy J on 12-Mar-13 06:44 AM

I need to retrieve the count of repetitive rows(ntext data type) in a table and the row data. This column contains Chinese and Japanese characters. These characters appear like question marks once cast to nvarchar(max). So if use group by on this column, its getting grouped with respect to the question marks when I need these to be grouped according to the Chinese or Japanese messages. My query is as follows.

SELECT Message, COUNT(*) TotalCount
FROM ipi_messagecustomization 
GROUP BY CAST([message] AS varchar(max))  WITH ROLLUP 
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC 

Kindly help. thanks all !!

Robbe Morris replied to Nancy J on 12-Mar-13 10:14 AM
You are casting as varchar which doesn't support those characters.  Did you intend to case to nvarchar?
Nancy J replied to Robbe Morris on 13-Mar-13 07:28 AM
I did not intend to use nvarchar.. I was just seeking for an alternative.. Now I found a solution!! Thanks all :)