How to create a new column in SELECT CASE when string contains Arabic words?

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.

Method 1

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

Method 2

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply