T-SQL code to convert nvarchar string to UTF-8 (for url-encoded url)
I need to create a URL string for an SSRS report (in order to link it to our CRM software). The title of the report is in Hebrew. When I post the url (with Heb) to Internet Explorer it doesn't recognize the address because it is not Percent-encoding (BTW, it works fine in Firefox). (Sending a URL in English only works like this.)
Anyway, I tried to do the encoding. I was able to convert it to a URI with UNICODE characters. I need to get the URI in UTF-8 . For example, the letter "י" should be converted to "% d7% 99" and not "% 05% D9".
I have included a link: A table with codes for your use if needed.
I need a conversion function \ encoding for 1 character. I can build the rest of the script / function for the whole line.
I used a script that used the master.sys.fn_varbintohexstr function. However, as I said, the results are not suitable for IE.
following:
SELECT master.sys.fn_varbintohexstr((CAST (N'י' AS varbinary)))
will get 0xd905 which I formatted in percentage encoding. Instead, I should get 'd7 99'.
completion: I am converting a Hebrew character to a URI encoding. I am getting the result in Unicode. I want> get utf8 result. Input = 'י'. Current output =% d9. Desired output =% d7% 99
How can I get these results?
source to share
I have dealt with several similar problems, and there are two approaches you may wish to consider; the first is to convert your data to HTML in a query and then render the result as HTML to RDL, the second is to use JQuery to identify those cells with the wrong value on the client and then convert that cell (again using JQuery). The advantage of the second option is that if the server rendering is running in Firefox, there is no conversion overhead involved. The downside is that if you don't render the report as HTML, it won't work.
For the first option in the select statement, you will need to change the corresponding column to get an nvarchar value that looks like
<span style="font=yourfont;" charset="UTF-8"><a href="yourdestination.com" target="_blank">linkname</a></span>
With this row as data, you then assign it to the appropriate columns (or cells if needed)
In the RDL designer, drag the placeholder for your field into the designer and right click on the placeholder and select the placeholder properties , then you can choose to display the content as HTML.
source to share