CODEWITHSUNDEEP
sqlsql-server
isaac tajolosa
isaac tajolosa

Reputation: 7

Get the highest value digit in a string

I would like to ask how I can get this the highest value digit in string:

Upvotes: 0

Views: 103

Answers (3)

Thiyagu
Thiyagu

Reputation: 1330

You can use MASTER..SPT_VALUES to attain this Rewquirement:

DECLARE @STRING NVARCHAR(MAX)='012546987';

SELECT MAX(SUBSTRING(A.B, V.NUMBER+1, 1)) 
FROM (SELECT @STRING B) A
JOIN MASTER..SPT_VALUES V ON V.NUMBER < LEN(A.B)
WHERE V.TYPE = 'P'

Upvotes: 0

Pranav Singh
Pranav Singh

Reputation: 20111

You can also use CHARINDEX :

select (case when CHARINDEX('9', stringvalue) > 0 then 9
             when CHARINDEX('8', stringvalue) > 0 then 8
             when CHARINDEX('7', stringvalue) > 0 then 7
             when CHARINDEX('6', stringvalue) > 0 then 6
             when CHARINDEX('5', stringvalue) > 0 then 5
             when CHARINDEX('4', stringvalue) > 0 then 4
             when CHARINDEX('3', stringvalue) > 0 then 3
             when CHARINDEX('2', stringvalue) > 0 then 2
             when CHARINDEX('1', stringvalue) > 0 then 1
             when CHARINDEX('0', stringvalue) > 0 then 0
        end)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270011

Assuming you want the highest valued digit in the string, then probably the simplest method is the brute force of a big case expression:

select (case when col like '%9%' then 9
             when col like '%8%' then 8
             when col like '%7%' then 7
             when col like '%6%' then 6
             when col like '%5%' then 5
             when col like '%4%' then 4
             when col like '%3%' then 3
             when col like '%2%' then 2
             when col like '%1%' then 1
             when col like '%0%' then 0
        end)

Upvotes: 5

Related Questions