Recently, I was reached out by one of the my financial technology clients based out of the USA who has just launched their services in India. Their system worked fine in most of the area but the challenge was to display the Indian currency sign. They really did not want to do custom code their system or change collation or anything. Just instead of US Dollar Sign ($), they wanted to display it with Indian Rupee Sign (₹). Let us learn today how we can Display Rupee Symbol in SSMS.
If your system is set up to use datatype money or decimal or int, you can use the language encoding to display dollar or rupee Sign next to it. Here is a very simple example of it.
DECLARE @currency money = 1234.56; SELECT @currency NoFilter, FORMAT(@currency, 'C', 'en-us') 'en-us', FORMAT(@currency, 'C', 'en-in') 'en-in', FORMAT(@currency, 'C', 'hi-in') 'hi-in';
In the above format function, we have the first parameter the value, the second parameter is the format and the third parameter is culture.
FORMAT ( value, format [, culture ] )
When you run the above code, you will get the following results.
You may notice in the resultset there is a very subtle difference in the resultsets. While we are looking at the result as en-in there is a small gap between rupee sign and amount whereas when hi-in is used there is no difference space between the rupee sign and amount. This is because how each of the culture info is encoded.
Here is an example of a couple of other Indian languages as well, where you can also show the Rupee sign.
DECLARE @currency money = 1234.56; SELECT @currency NoFilter, FORMAT(@currency, 'C', 'hi-in') 'hi-in', FORMAT(@currency, 'C', 'gu-in') 'gu-in', FORMAT(@currency, 'C', 'kn-in') 'kn-in';
Here is the result of the query ran above.
As the sign of rupee is the same in all the languages, you do not see any difference in the result. However, the result may differ when there is a different string besides rupee sign for example dates.
DECLARE @currency datetime = GETDATE(); SELECT FORMAT(@currency, 'D', 'en-us') 'en-us', FORMAT(@currency, 'D', 'hi-in') 'hi-in', FORMAT(@currency, 'D', 'gu-in') 'gu-in', FORMAT(@currency, 'D', 'kn-in') 'kn-in';
When you run the above statement, you get the following results.
As a result, now you can see how each of the cultures makes different to display results.
Reference: Pinal Dave (https://blog.sqlauthority.com)