C++ stoi equivalent in SQL Server's T-SQL I'm looking for something I can use in Microsot SQL Server that would act the same way the stoi function in C++ acts.  Specifically, I'm looking to be able to grab numerics at the beginning of the string, and ignore non-numerics afterwards.  I need this to sort the following strings and need them to show up in the following order:


  99ABC
  123ABCD
  ABC
  DEFGH


So I was thinking if I had something like STOI, I can do:

ORDER BY CASE WHEN (ISNUMERIC(LEFT(TEXT, 1))) THEN STOI(TEXT) ELSE 999999 END, TEXT


I can precompute the results of STOI (or the results of the above CASE statement) and store into an extra INT column nightly.  So I don't really care about real-time performance.  I guess I'm just looking for a way without having to check all the chars of the string:

DECLARE @numEnd INT = 0 
WHILE (ISNUMERIC(LEFT(TEXT, @numEnd)) BEGIN
     SET @numEnd = @numEnd + 1 
END
DECLARE @stoi INT = CAST(LEFT(TEXT, @numEnd) AS INT)
DECLARE @Test VARCHAR(32)

SET @Test = '99ABC 123ABCD ABC DEFGH'

DECLARE @stoi INT = TRY_CONVERT(INT, LEFT(LTRIM(@Test), PATINDEX('%[^0123456789]%', LTRIM(@Test + ';')) - 1))

SELECT @stoi



  99