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
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 SturkenboomPosted Oct 3, 2014, 9:09 AM
if Cast(SUBSTRING(@strAlphaNumeric,@intAlpha, len(@strAlphaNumeric)) as int) > Cast(SUBSTRING(@strAlphaNumeric1,@intAlpha, len(@strAlphaNumeric1)) as int)
begin
select SUBSTRING(@strAlphaNumeric,1,@intAlpha-1)
end
else
begin
select SUBSTRING(@strAlphaNumeric1,1,@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)