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

Question

How to write Query for Get MAX value from This type of values 'A1.....A10.....A100.....A1000' How to get the max value After 'A'

Suraj Kumar · Answer

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.

Laxmidhar Sahoo · Answer

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0735fc88-d5f6-4171-8c10-7a410fe154e1/how-to-sort-alpha-numeric-data-in-sql?forum=sqlsearch

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

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (2)