Sql-server – Natural (numeric) sort on string

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc    
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.

The reason your function takes ages is because you have empty values for actual in UTF8Encoding. The patindex expression returns 1 when you check for an empty actual so you never exit the inner loop. You can fix that by adding and actual <> '' to the query against UTF8Encoding. Next issue is where you use @expected as parameter to nchar(). The parameter should be an integer so if you remove nchar() your code returns something but I don't think it is what you are looking for. WilcoxonÃ is translated to WilcoxonÁƒƒÁ††™.

Another approach you can try is to use the XML capabilities in SQL Server. XML in SQL Server is UTF-16 but it is able to load UTF-8 encoded strings and that can be used.

Concatenate your string with a UTF-8 xml declaration and use the value() function to fetch the value from the constructed XML.

I guess you eventually want to use this on a table so here is an example that uses a table variable.

declare @T table(InputString varchar(max))

insert into @T values
('åäöÅÄÖ'),
('WilcoxonÃ')

select cast('<?xml version="1.0" encoding="UTF-8"?>'+T.InputString as xml).value('text()[1]', 'nvarchar(max)') as Value
from @T as T

Result:

Value
---------------
åäöÅÄÖ
WilcoxonÃ