from my code i want to check which is greater

Question

Declare @strAlphaNumeric varchar (50) = 'ARPA/B147 '

Declare @strAlphaNumeric1 varchar (50) = 'ARPA/B146 '

DECLARE @intAlpha INT

SET @intAlpha = PATINDEX('%[0-9]%', @strAlphaNumeric)

SET @intAlpha = PATINDEX('%[0-9]%', @strAlphaNumeric1)

select Cast(SUBSTRING(@strAlphaNumeric,@intAlpha, len(@strAlphaNumeric)) as int)

select Cast(SUBSTRING(@strAlphaNumeric1,@intAlpha, len(@strAlphaNumeric1)) as int)

select SUBSTRING(@strAlphaNumeric,1,@intAlpha-1)

select SUBSTRING(@strAlphaNumeric1,1,@intAlpha-1)

Running the above code shows output as follows

147

146

ARPA/B

I want to check 147 or 146 which is greater.

From the above 147 is greater.

for getting a output 147.

from my above code what changes i have to made to get 147 output.

Wim Sturkenboom · Answer

Not quite sure if this is what you're looking for if Cast(SUBSTRING(@strAlphaNumeric@intAlpha len(@strAlphaNumeric)) as int) > Cast(SUBSTRING(@strAlphaNumeric1@intAlpha len(@strAlphaNumeric1)) as int) begin select SUBSTRING(@strAlphaNumeric1@intAlpha-1) end else begin select SUBSTRING(@strAlphaNumeric11@intAlpha-1) end Note that the highlighted line is not very useful as the last line 'overwrites' DECLARE @intAlpha INT SET @intAlpha = PATINDEX('%[0-9]%' @strAlphaNumeric) SET @intAlpha = PATINDEX('%[0-9]%' @strAlphaNumeric1)