How to get number from String sql

Question

I want to get number from String in sql Example: mn21 or mnp100, how to get number from "mb21" or "mnp100". Then, i will sort order by numeric

Answer 1

You can use PATINDEX to search for the start index of the number and then RIGHT to get the remaining numbers:

WITH SampleData(string) AS(
    SELECT 'mn21' UNION ALL
    SELECT 'mnp100'
)
SELECT
    string, 
    Number = CAST(RIGHT(string, LEN(string) - PATINDEX('%[0-9]%', string) + 1) AS INT)
FROM SampleData
ORDER BY Number