Call. Net assembly in SQL Server

It is a coincidence and helpless move to use this thing. Accidentally inserted some data after httputility. urlencodeunicode in the database. Some fields in the database are structured like % uxxxx.

Search for the half-day T-SQL urldecode function, found that the above Unicode is not supported. I am not familiar with T-SQL, time is also very urgent.

To call httputility. urldecode in a T-SQL, you need to contact the person who calls it. Check it, probablyCodeAs follows. If this item is used up, forget it. Remember it for future reference.

Create a DLL and a class. The Code is as follows.

 
Public Partial ClassSystemweb {[sqlmethod]Public StaticSqlstring urldecode (String Value){Return NewSqlstring (httputility. urldecode (Value));}}
 

Put it on the database server.

Then run the following T-SQL code

 

 Exec Sp_configure 'Show advanced options' , '1' ; Go  Reconfigure ; Go   Exec Sp_configure 'Clr enabled' , '1'   Reconfigure ; Go   Alter   Database Dbname Set Trustworthy On   Go   Create Assembly [system. Web] From   'C: \ windows \ Microsoft. NET \ framework64 \ v2.0.50727 \ System. Web. dll'   With Permission_set = unsafeGo   Create Assembly SQL _clr_url From   'D: \ sqlclr. dll'   With Permission_set = unsafe Go  Create   Function Sqlurldecode (@ urlstr nvarchar (4000 )) Returns Nvarchar (4000) As   External Name SQL _clr_url. [sqlclr. systemweb]. urldecode Go    

Then you can call this function in the T-SQL.

Select DBO. sqlurldecode (name) from table

For safety, when your ass is wiped out, remember to turn off the CLR.