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)
Best Answer