CODEWITHSUNDEEP
sqlsql-server
Carlos Mendieta
Carlos Mendieta

Reputation: 872

A query that will search for the highest numeric value in a table where the column has an alphanumeric sequence

I have a column (XID) that contains a varchar(20) sequence in the following format: xxxzzzzzz Where X is any letter or a dash and zzzzz is a number.

I want to write a query that will strip the xxx and evaluate and return which is the highest number in the table column.

For example:

aaa1234
bac8123
g-2391

After, I would get the result of 8123

Thanks!

Upvotes: 1

Views: 95

Answers (5)

viejoEngineer
viejoEngineer

Reputation: 404

You can also use this method

CREATE TABLE #Tmp
(
  XID VARCHAR(20)
)
INSERT INTO #Tmp(XID)
VALUES ('aaa1234'), ('bac8123'), ('g-2391') 

SELECT MAX(RIGHT(XID, LEN(XID) - 3))
FROM #Tmp

Upvotes: 1

BuddingProgrammer
BuddingProgrammer

Reputation: 51

Try:

Select MAX(RIGHT(XID,17))
from table

Upvotes: 1

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

Using PATINDEX you can achieve it, like this -

DECLARE @test table
  (
     id    INT,
     player varchar(100)
  )

INSERT @test
VALUES (1,'aaa1234'),
       (2,'bac8123'),
       (3,'g-2391')

SELECT
    MAX(CONVERT(INT, LTRIM(SUBSTRING(player, PATINDEX('%[0-9]%', player), LEN(player)))))
FROM @test

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270011

A bit painful in SQL Server, but possible. Here is one method that assumes that only digits appear after the first digit (which you actually specify as being the case):

select max(cast(stuff(col, 1, patindex('%[0-9]%', col) - 1, '') as float))
from t;

Note: if the last four characters are always the number you are looking for, this is probably easier to do with right():

select max(right(col, 4))

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28900

Using Numbers table

    declare @string varchar(max)
    set @string='abc1234'

    select top 1 substring(@string,n,len(@string))
    from
    numbers
    where n<=len(@string)
    and isnumeric(substring(@string,n,1))=1
    order by n

Output:1234

Upvotes: 1

Related Questions