right to left string in SQL server

Question

Consider below query containing both Persian(a right to left language) and English(a left to right language):

SELECT 'نرم افزار SQL سرور'

the required result is this string :

سرور SQL نرم افزار

Is there any function or any other way to converting string from ltr to rtl??

Answer 1

It is required to add N before string literal: SELECT N'نرم افزار SQL سرور'. This is needed to correctly interpret contained Unicode characters. (Source)

Important: In some cases, please avoid using standard copy-paste in order to put SELECT into SSMS command window. This could affect the RTL/LTR order. Instead, try to open correctly composed file using File > Open.

And regarding your comment:

the result should be : سرور SQL نرم افزار`

I admit I understand RTL writing system only partially, but from what I can see, Persian words are put to the output exactly in order as you entered them (even if reading right to left). Could you show me based on Unicode Bidirectional Algorithm or similar standards document why the word order should be changed by SQL Server? Shouldn't be change you expect made by preprocessing on another place, sending expected string form SELECT N'سرور SQL نرم افزار'? I see no point why just SQL SELECT should perform the change. If it did, what would happen if you feed result of such a SELECT into another SELECT? Another transformation? I have reasons to think that SQL server is interpreting your input technically correctly.

Hint: maybe you can try to surround your RTL text by different Directional formatting characters.

Please try the same SELECT with MySQL server at SQL Fiddle. Different server and technology, but the same result as Microsoft SQL Server gave.

Result from SSMS with MS SQL Server:

Conclusion: in order to get expected result, please form the input accordingly.

Related: Transformation of word order you expected can be done by appropriate settings in user interface.

Answer 2

When we add digit with english this will again not work following solution will work

SELECT nchar(8234)+ N' 33-M ' + N'کلینک کمرہ نمبر' +nchar(8236) + N'میں تشریف لائیں'