2

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
3

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 VARCHAR string.

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 Arabic_CI_AI_KS_WS.

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 VARCHAR:

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
6
  • 2
    Hi there. When working with NVARCHAR data, the collation is entirely irrelevant to determining which characters can be stored: it's all of them as it's Unicode. Neither the COLLATE clause, nor even the CAST, are necessary here. The only relevant change you made to the O.P.'s code is adding the N prefix on the string literals. I explain more in my answer. – Solomon Rutzky Apr 23 at 19:52
  • Hi JD didn’t I write a comment saying you needed an N’ about 6 hours ago ? It seems to have disappeared – Stephen Morris - Mo64 Apr 23 at 20:07
  • @StephenMorris-Mo64 Yes and actually I replied with a comment thanking you for the reminder lol. I'm guessing a mod cleaned up the comments and likely will happen again for your last comment and this one too. 🙃 – J.D. Apr 23 at 21:21
  • 1
    @SolomonRutzky Ah yes you're right. I'm noobing it up all over here since it's not often I need to work with the NVARCHAR data type, surprisingly. I've corrected my answer, and you have my upvotes for your in depth and helpful answer as well. Thanks! – J.D. Apr 23 at 21:29
  • Still wrong, just remove cast and collate, you only need N'' – Charlieface Apr 24 at 22:26
6

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- رجوع

(the -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)

When using NVARCHAR (including 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 'قدوم' returned ???? 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 CAST to 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 — *= and =* — 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
2
  • 3
    +1 for the answer but a minor correction needed: old style comma join has not and will likely never be deprecated as it is standard SQL. What has been deprecated are the non-standard old style outer joins (*= and =*). – ypercubeᵀᴹ Apr 23 at 18:02
  • 1
    @ypercubeᵀᴹ Thanks for pointing that out. I misremembered and was being lazy when I found the documentation referring to the removal of *= and =* and didn't check the current documentation to see if there was a deprecation notice of the basic INNER / CROSS join syntax. I have updated my answer accordingly, and this time included the link to the documentation. – Solomon Rutzky Apr 23 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.