2

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)
  • What is STOI's result if the string doesn't start with a number? – Denis Rubashkin Aug 23 '18 at 14:38
  • @DenisRubashkin zero I believe, or null. But since I need non-numerics at the end, that why I added the ELSE 99999 at the end – John Aug 23 '18 at 15:30
1
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.