All we need is an easy explanation of the problem, so here it is.
I have a problem with a select case when statement, I would like to add a new column in the select case when statement, I got the result but with
????? because I have set the column to Arabic words, I have tried to convert the content of the new column to
nvarchar(55) unfortunately, I got the same result.
How can I get the correct result?
SELECT case when ( CAST(o.startTime as time(7)) > cast(Start as time(7))) then cast('قدوم' as nvarchar(55)) else cast('رجوع' as nvarchar(55)) end as stat, userId, FirstName from Users, TimeTable
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
In this case because you’re using an
NVARCHAR data type in your cast which supports the entire Unicode character set, all you’re missing from your ad-hoc string’s prefix is
N character to signify to SQL Server to treat the string as
NVARCHAR, at which point makes your
CAST() function call redundant and not needed either. E.g.
SELECT N'Some Text' will produce an
NVARCHAR string as opposed to just
SELECT 'Some Text' which produces a
Solomon Rutzky’s answer provides a clear-cut example of this as well, with more insightful information.
For anyone who runs into this issue while using
VARCHAR (or similar) data types, the below are the droids you’re looking for:
You need to use an Arabic character supporting collation in the column, for the table, in the database, or at the server instance level such as
This StackOverflow answer has good information on it.
For ad-hoc queries and values you should be able to use the
COLLATE function as needed. For example with your above query if we needed to use
SELECT case when (CAST(o.startTime as time(7)) > cast(Start as time(7))) then cast(N'قدوم' COLLATE Arabic_CI_AI_KS_WS as varchar(55)) else cast(N'رجوع' COLLATE Arabic_CI_AI_KS_WS as varchar(55)) end as stat, userId, FirstName from Users,TimeTable
Since you are using Unicode / UTF-16 (i.e.
NVARCHAR), you do not need either the
CAST or even an
Arabic_* collation. The only requirement for string literals is to prefix them with a capital-"N":
SELECT 'قدوم' COLLATE Arabic_CI_AS, '-o-' AS [-o-], 'رجوع' COLLATE Arabic_CI_AS; -- ???? -o- ???? SELECT N'قدوم' COLLATE French_CI_AS, '-o-' AS [-o-], N'رجوع' COLLATE French_CI_AS; -- قدوم -o- رجوع
-o- field is used as a separator to prevent the Bi-Direction formatting, inherent with Arabic and Hebrew code points, from displaying the second column on the left in the T-SQL comment below the query when rendered in HTML)
NCHAR and the should-never-be-used
NTEXT), the encoding is always UTF-16, hence the character set never changes (it’s always Unicode, which is a single character set). In this case, the collation mainly affects sorting and comparisons (which are not being done here).
If you were using
VARCHAR, then an Arabic collation would be needed in order to set the code page in order to support the Arabic characters (Windows Code Page 1256).
The reason why your initial
???? is that the string literal was not prefixed with
N, making it a
VARCHAR string, in which case it was converted to the code page associated with the default collation of the current database you were using, which clearly was not an Arabic collation. This conversion happens as the query is initially parsed, which means that your string was already
???? before the cast operation converted it to
NVARCHAR. If you had been using a database that had an Arabic default collation, then your string literal would have worked even without the
N prefix, and the
NVARCHAR would have been just as unnecessary.
For more information on working with strings / encodings / Unicode / collations, please visit my site: Collations Info
P.S. I would recommend that you not be doing old-style JOINs such as
FROM TableA, TableB WHERE TableA.JoinColumn = TableB.JoinColumn. While that syntax has not been deprecated, the related outer join syntax —
=* — was actually removed in SQL Server 2005. So, for consistency (and personally, I would also throw in readability), you should instead use the
INNER JOIN clause. Also, it’s always best to prefix object names with their Schema name. The end result is:
FROM dbo.TableA INNER JOIN dbo.TableB ON TableB.JoinColumn = TableA.JoinColumn
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂