How to get max value from like "A1...A10....A100"

If your string always contain A as character then you can try this

After getting the values you can cast it as integer as shown below.

You can also try the following query.

SELECT LEFT(SUBSTRING('A100', PATINDEX('%[0-9.-]%', 'A100'), 8000),
PATINDEX('%[^0-9.-]%', SUBSTRING('A100', PATINDEX('%[0-9.-]%', 'A100'), 8000) + 'X') -1)

SELECT Cast( LEFT(SUBSTRING('A100', PATINDEX('%[0-9.-]%', 'A100'), 8000),
PATINDEX('%[^0-9.-]%', SUBSTRING('A100', PATINDEX('%[0-9.-]%', 'A100'), 8000) + 'X') -1)
as Integer) as OnlyNumbers

If you want to get the maximum value with added one then you can write the following query.

SELECT Cast( LEFT(SUBSTRING('A100', PATINDEX('%[0-9.-]%', 'A100'), 8000),
PATINDEX('%[^0-9.-]%', SUBSTRING('A100', PATINDEX('%[0-9.-]%', 'A100'), 8000) + 'X') -1) + 1
as Integer) as OnlyNumbers

You can pass the column name also in place of the hardcode values.

Hope this will help you.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo6y ago
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0735fc88-d5f6-4171-8c10-7a410fe154e1/how-to-sort-alpha-numeric-data-in-sql?forum=sqlsearch
Suraj Kumar6y ago
If your string always contain A as character then you can try this
Select replace('A1', 'A', '')
Select replace('A10', 'A', '')
Select replace('A100', 'A', '')
After getting the values you can cast it as integer as shown below.
Select Cast(replace('A1000', 'A', '') as Integer)
You can also try the following query.
SELECT LEFT(SUBSTRING('A100', PATINDEX('%[0-9.-]%', 'A100'), 8000),
           PATINDEX('%[^0-9.-]%', SUBSTRING('A100', PATINDEX('%[0-9.-]%', 'A100'), 8000) + 'X') -1)
SELECT Cast( LEFT(SUBSTRING('A100', PATINDEX('%[0-9.-]%', 'A100'), 8000),
           PATINDEX('%[^0-9.-]%', SUBSTRING('A100', PATINDEX('%[0-9.-]%', 'A100'), 8000) + 'X') -1)
            as Integer) as OnlyNumbers
If you want to get the maximum value with added one then you can write the following query.
SELECT Cast( LEFT(SUBSTRING('A100', PATINDEX('%[0-9.-]%', 'A100'), 8000),
           PATINDEX('%[^0-9.-]%', SUBSTRING('A100', PATINDEX('%[0-9.-]%', 'A100'), 8000) + 'X') -1) + 1
            as Integer) as OnlyNumbers
You can pass the column name also in place of the hardcode values.
create table YourTable
(
  Col varchar(100)
);

insert into YourTable values
('AB ABCDE # 123'),
('ABCDE# 123' ),
('AB: ABC# 123'),
('A1005')

Select stuff(Col, 1, patindex('%[0-9]%', Col)-1, '')
from YourTable
Hope this will help you.